Controling the totaling method for aggregated columns in VA 7.5

[MarkusWeick]

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?

[HunterT_SAS · Accepted solution]

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. 

[MarkusWeick]

Thanks @HunterT_SAS,

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

Best,

Markus

[MarkusWeick]

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.)

[HunterT_SAS]

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

[Sam_SAS]

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

[MarkusWeick]

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

[acordes]

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.

[MarkusWeick]

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

[acordes]

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