topic Re: Controling the totaling method for aggregated columns in VA 7.5 in SAS Visual Analytics

Controling the totaling method for aggregated columns in VA 7.5

MarkusWeick — Wed, 26 Oct 2022 13:55:54 GMT

Hi, VA specialists,

how does VA decide which method it uses to total an aggregated column?

Some trials show that you can get different results for the same data, depending on the syntax. In one case VA uses the sum in the other the average:

Case 1:

Case 2:

Same data but different method of totaling:

The only difference was the syntax of the aggregated column “Calc”:

Case 1:

Case 2:

The only difference between the two is the Aggregation method in the else-clause. It doesn’t come into action, because the if-clause is parameterized accordingly.

Is there a general rule, so that the desired aggregation method can be achieved?

Re: Controling the totaling method for aggregated columns in VA 7.5

HunterT_SAS — Wed, 26 Oct 2022 14:11:13 GMT

In your example here, what's happening is that it's actually using the Total value (79.20) when it evaluates the condition, so it will run the Else part of your calculated item when it's figuring out the Total line.

Re: Controling the totaling method for aggregated columns in VA 7.5

MarkusWeick — Wed, 26 Oct 2022 14:42:40 GMT

Thanks @HunterT_SAS,

I just adjusted the if-clause and understood what you mean.

Best,

Markus

Re: Controling the totaling method for aggregated columns in VA 7.5

Sam_SAS — Wed, 26 Oct 2022 14:44:55 GMT

Edit: Looks like Hunter came to basically the same conclusion

My 7.5 image is acting up so I have only tested this on Viya, but the behavior might be the same.

From what I can tell, it looks likes the last aggregation operator (Sum, Avg, etc) in the expression is used for the Total row. So in Case 1, if you want average to be the Total aggregation you could invert the logic of the expression so that Avg is the ELSE rather than the RETURN.

That is just my observation from testing on Viya, the actual code may be more complicated than this and the behavior on 7.5 might be different.

Sam

Re: Controling the totaling method for aggregated columns in VA 7.5

MarkusWeick — Wed, 26 Oct 2022 14:55:40 GMT

Hi @Sam_SAS,

Thanks. I just reversed the logic. And it confirmed what @HunterT_SAS wrote. For the total, the if clause is evaluated on the underlying total. So it's not the last aggregation method in the syntax which determines the total aggregation.

Best,

Markus

Re: Controling the totaling method for aggregated columns in VA 7.5

MarkusWeick — Wed, 26 Oct 2022 16:47:38 GMT

Hi @HunterT_SAS ,

now that I have understood (thanks to your explanation) that also “the total” is subject to the formula for the aggregated column I adjusted the formula again:

For the IF-clause I choose all values of the “Department” column, so the only line not included is the total line, for which I can choose the aggregation method in the else clause.

This gives me the desired result:

(Averages for all departments, but the sum, not the average, in the total.)

Re: Controling the totaling method for aggregated columns in VA 7.5

HunterT_SAS — Wed, 26 Oct 2022 16:52:21 GMT

Ohh I like that a lot, that's a nice workaround! Definitely not something I have ever considered before.

Re: Controling the totaling method for aggregated columns in VA 7.5

acordes — Wed, 26 Oct 2022 18:03:07 GMT

Hi all.
In my understanding you can can have it easier by working with base condition average by group and in the specific condition you specify Sum by group for the total crossing intersection.

Re: Controling the totaling method for aggregated columns in VA 7.5

MarkusWeick — Wed, 26 Oct 2022 19:06:58 GMT

Hi @acordes,

in my VA 7.5 there is no "base expression" or "scope" choice.

I think, I have to argue stronger to get Viya.

Best,

Markus

Re: Controling the totaling method for aggregated columns in VA 7.5

acordes — Wed, 26 Oct 2022 19:26:33 GMT

Get Viya. You won't regret.
Viya is super complete and lightning fast.