- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
We have a cross table that can be drilled down and it has the Sales value for the month and the Actual YTD for the monthly cumulative. However, in Sept there was no Sales, and so no entry for that month. So it’s not showing an Actual YTD for that month either then, in other words September doesn't appear in the table.
Formula for Actual YTD (€) is CumulativePeriod(_Sum_, Sales,_IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, _ByYear_, 0, 1,_Full_, {Date})
Can we adjust the formula to force an entry for September even is there is no Sales data for that month?
Regards,
Aoife.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This is a data organization and recognition task. I don't think it will be resolved by a modification of the formula you present.
If, in your data, you can generate a dummy record with sales=0 for missing months, probably the sas coding you are using would work.
But, since you have not provided the code, nor representative data in the form of a sample data step, we can't offer advice in the form of sas program statements. I think you will need to help us help you.
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello @A_Darcy ,
What version are you using? I tried to recreate your issue with VA8.5 and below is the Crosstab I got. Is this different from yours? In your source data does the Date variable still have September even though Sales doesn't have value in it?
Actual YTD (€) = CumulativePeriod(_Sum_, 'Sales_missing'n,
_IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, _ByYear_, 0, 1,
_Full_, {Date})
I have attached the data I used.
Best regards,
Petri