topic Re: Month Missing from Crosstab where not data is present and using CumulativePeriod formula in SAS Visual Analytics

Month Missing from Crosstab where not data is present and using CumulativePeriod formula

A_Darcy — Tue, 14 Jul 2020 15:58:32 GMT

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.

Re: Month Missing from Crosstab where not data is present and using CumulativePeriod formula

mkeintz — Tue, 14 Jul 2020 17:45:07 GMT

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.

Re: Month Missing from Crosstab where not data is present and using CumulativePeriod formula

PetriRoine — Wed, 15 Jul 2020 07:49:28 GMT

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