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:
Same data but different method of totaling:
The only difference was the syntax of the aggregated column “Calc”:
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?
I just adjusted the if-clause and understood what you mean.
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.)
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.
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.
in my VA 7.5 there is no "base expression" or "scope" choice.
I think, I have to argue stronger to get Viya.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.