☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Barite | Level 11

## Controling the totaling method for aggregated columns in VA 7.5

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?

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
1 ACCEPTED SOLUTION

Accepted Solutions
SAS Super FREQ

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

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.

9 REPLIES 9
SAS Super FREQ

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

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.

Barite | Level 11

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

Thanks @HunterT_SAS,

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

Best,

Markus

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
Barite | Level 11

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

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

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
SAS Super FREQ

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

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

SAS Super FREQ

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

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

Barite | Level 11

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

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

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
Rhodochrosite | Level 12

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

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.
Barite | Level 11

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

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

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
Rhodochrosite | Level 12

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

Get Viya. You won't regret.
Viya is super complete and lightning fast.
Discussion stats
• 9 replies
• 405 views
• 6 likes
• 4 in conversation