topic Re: YTD calculation issue | when not all category values exist for a given month. in SAS Visual Analytics

YTD calculation issue | when not all category values exist for a given month.

rodrigo_pereira — Mon, 19 Oct 2020 14:02:18 GMT

Hello,

although I have done a research on the YTD topic, I haven't found the answer to my specific question.
I'm using the cumulative period function on VA 7.4 (screenshot 1 below) to calculate and it works (almost) fine except in one circumstance:

When, for a given month, on the source data, we cannot find values for all the possible values of a given category (used in the crosstab), they will not be displayed (even if they should have YTD value)?
Please find the screenshots 2 and 3 below so you can understand better what I mean.
I would expect that, even if we don't have the MTD value in the source table for September, the value would appear anyway in the crosstab as there are values for August and the crosstab is displaying the YTD (cumulative period function).

Is this a issue that is already corrected in later versions of VA (e.g. 8.5)?
Or I'm obliged to calculate YTD on the data preparation step?

Or should I create "dummy" values for all the categories values that I'm using on the crosstab?

Thank you in advance for your help.

All the best,

Rodrigo

screenshot 1:

screenshot 2 (YTD for September).

Not all DIRECTION values are displayed, as we can see on the screenshot 3 with the YTD for August

screenshot 3 (YTD for August).

The value is correct and the three DIRECTION values are displayed as there are values on the source table for August 2020.

Re: YTD calculation issue | when not all category values exist for a given month.

Sam_SAS — Wed, 21 Oct 2020 13:19:55 GMT

Hi Rodrigo,

When I test this on 8.5, I do not get missing values. I am not sure about 7.5.

If you can create dummy values (replace missing with 0), that should resolve the issue.

Sam

Re: YTD calculation issue | when not all category values exist for a given month.

rodrigo_pereira — Wed, 21 Oct 2020 13:31:41 GMT

Hi Sam,

thank you for the good news regarding the 8.5 version!

I also suspected that this should had already been solved by SAS on recent releases.

Finally, I've discovered that I would need the calculation on the data model for other reasons (e.g. drill-through to the detail ...) and I've calculated YTD on the data preparation step.

Thank you for the insight.

BRs,

Rodrigo