Pivot Aggregation - odd results occurring

[#1]

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

[tresesco · Accepted solution]

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

[#3]

That has done the trick.

Much appreciated!