SAS Visual Analytics: How can a cumulative total for two years be displayed in a line chart?

[klroesner]

Hi,

we want to display the cumulative totals for the years 2022 and 2023 in a line chart. Unfortunately, this does not work

Max view of the diagram:

Cumlative Totals definition (value_agg):

. Only when a filter is applied to the year 2022 are the correct values displayed.

Can everyone give us a hint as to what we are doing wrong?

Best Regards

Klaus

[TSBruce · Accepted solution]

I think you may need to use a Period to Date calculation using the full data (not using the default ToToday)  instead of a Cumulative Total.  Here is my expression:

CumulativePeriod(_Sum_, Value, IgnoreAllTimeFrameFilters, 'Year'n, Inferred, ByYear, 0, 1, Full)

And here is my results in a line chart:

Please let me know if this gets you what you need.

[klroesner]

Hi TSBruce, That looks wonderful! 😍  But I can't quite reproduce it yet. 

I created the CAS Table as follows:

data public.test_agg1 (promote=yes);
	set cpm0978.test_agg;
	attrib dyear length=8 format=year.;
	attrib dmonth length=8 format=month.;
	dyear=mdy(month, 1, year);
	dmonth=mdy(month, 1, year);
run;

Unfortunately, the YTD metric only generates missing values and get a error message:

Error message: "A mix of date references has resulted in missing values being returned for a period calculation. Consider creating the date references by duplicating the data element and then adjusting the properties of the duplicated data element. For example, you can change the format."

Definition: CumulativePeriod(_Sum_, 'value'n, _IgnoreAllTimeFrameFilters_, 'dyear'n, _Inferred_, _ByYear_, 0, 1, _Full_, {Date})

Can you give me a hint as to what I'm doing wrong?

I'm looking forward to your reply.

Klaus

[klroesner]

By the way we are using SAS Viya 3.5 ...

[klroesner]

Hello Bruce! A colleague advised me to duplicate the month column and use the Year. format. And what can I say? It works! Bruce, you made our day! 

And it beautifully demonstrates the power of SAS VA! 🙂