Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm doing some aggregateion on a pivot table and was wondering if someone could explain 1 thing that is causing an issue.
I have a table as below:
Code1 | Code2 | AlphaCode | aggregate Count of Code 2 | aggr(sum(Value), Code2) | Max Value Code 2 |
9200059 | 50045705 | WPL | |||
SEA | 4 | 4 | 75 | ||
2371172 | APQ | 3 | |||
2370752 | DEF | 3 | |||
2371811 | GHI | 75 |
I am trying to get the max value of sum(value) which I can get and put it in "Max Value Code 2". The issue I have is where it is placed. Why does the 75 not get put on the bottom row alonside the correspondingsum(value) equalling 75?
This is causing issues as I want to add more columns stemming from this calculation but it does not work here.
My expression for the last column is:
aggr(max(aggr(sum(Value), Code2)), Code1)
If anyone has ideas or suggestions it would be much appreciated.
Thanks
Brendan
To answer your why: Because for all the rows your output is same, and for such cases qv puts the value in the first row unless you have used NoDistinct keyword in the expression. But using that keyword would not give you what you want, it would populate all the rows with same values. To get your output, you have to put an additional condition to check the equlity of values between previous column and this one.
Somewhat like:
=If(Column(2)= aggr( NoDistinct max(aggr(sum(Value), Code2)), Code1), aggr( NoDistinct max(aggr(sum(Value), Code2)), Code1))
To answer your why: Because for all the rows your output is same, and for such cases qv puts the value in the first row unless you have used NoDistinct keyword in the expression. But using that keyword would not give you what you want, it would populate all the rows with same values. To get your output, you have to put an additional condition to check the equlity of values between previous column and this one.
Somewhat like:
=If(Column(2)= aggr( NoDistinct max(aggr(sum(Value), Code2)), Code1), aggr( NoDistinct max(aggr(sum(Value), Code2)), Code1))
That has done the trick.
Much appreciated!