How can I use above() with aggr()

[#1]

Hi Qlikviewers. I am currently trying to solve an issue where when I create a table, I can find a value im looking for (max of column2-column1), but when I try to apply that logic and create a temp table with aggr, there is no way to sort the table, so the value I get doesn't match up.

I am posting an example below of what I'm trying to do. The "KS" number should match the max of the KS column in the table (42.01%)

[sunny_talwar · Accepted solution]

I think to get the same number using the aggregate function, you will need to sort the Score dimension in the script. Something like this:

Table:

LOAD Score as Score1,

    Booked,

    Bad

FROM

[KSExample.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD Score1 as Score,

  Booked,

  Bad

Resident Table

Order By Score1;

DROP Table Table;

The reason is that Aggregate always uses the Load sort order. The result I got was very close, but not sure why it doesn't completely match.

[#3]

Thanks Sunny. Strange it isn't a perfect match. I will see if others can diagnose that, but this answer was helpful

[sunny_talwar]

I actually extracted the excel file from the application itself, can you try with the data file you have and the script I provided to see what happens? Or if you can provide the excel file, I can check it at my end as well.

[#5]

Yeah I just tried it with the raw excel file and the numbers match exactly.

This is a nice workaround but I do wish there was a way to sort within the aggr function, rather than having to rely on data load order.

Thanks!