BookmarkSubscribeRSS Feed
A_Darcy
Calcite | Level 5

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.

 

 

 

2 REPLIES 2
mkeintz
PROC Star

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

--------------------------
PetriRoine
Pyrite | Level 9

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?

 

Capture.PNG

 

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 798 views
  • 1 like
  • 3 in conversation