topic Re: Adding the percentual variance between columns on crosstabs in SAS Visual Analytics

Adding the percentual variance between columns on crosstabs

Renan_Crepaldi — Wed, 05 Aug 2020 16:21:15 GMT

Hi, everyone!

I'm trying do add a column with a simple calculation on my crosstab, but I haven't find a way to do it.

I want to make a percentual variance between the two periods I'm comparing (2020 vs. 2019). It should be like this:

I don't know if this changes anything, but I've selected the option to show the headers of the measures on the rows.

Is there a way to do it on the crosstab? Or i should use another type of object?

Thanks!

Re: Adding the percentual variance between columns on crosstabs

PetriRoine — Tue, 18 Aug 2020 07:29:55 GMT

Hello @Renan_Crepaldi ,

Yes, you can do this in Crosstab. You just need to use aggregated measure:

( Sum [_ByGroup_] ('Y2020'n) / Sum [_ByGroup_] ('Y2019'n) ) - 1

And here is how it looks like plus the source data I used:

I hope this helps,

Br,

Petri

Re: Adding the percentual variance between columns on crosstabs

Renan_Crepaldi — Tue, 18 Aug 2020 14:46:53 GMT

Hello, @PetriRoine! Thanks for replying.

The problem is that my data doesn't have the KPIs separated by year. I needed to use the crosstab to split each KPI by putting the year on the column.

I've attached a sample to make it easier to understand (should've done this before, sorry).

If you need anymore information, please, let me know.

BR, Renan

Re: Adding the percentual variance between columns on crosstabs

PetriRoine — Wed, 26 Aug 2020 06:49:58 GMT

Hello @Renan_Crepaldi ,

With the data structure you have, personally I don't think it's doable with any out-of-the-box chart or custom build graph. The way I see it, you have two options to achieve this:

Adjust the data structure
If you have one of the newer versions of VA then you could use Data-Driven Content and any JavaScript charting framework to create this.

Best regards,

Petri

Re: Adding the percentual variance between columns on crosstabs

Renan_Crepaldi — Wed, 26 Aug 2020 21:24:18 GMT

Thanks for the tip, Petri.

I'll try to adapt the structure of my table.

Regards,
Renan