Cannot compare max value of a dimension and the inline value of the dimension

[serpentium]

i created a simple table to test what doesnt work on my app, let's say

Table:
LOAD * Inline [
clientID , clientreport
123 , 2
123 , 4
145 , 1
145 , 3
89 , 9
89 , 11
89 , 6
200 , 22
];

now, i cannot work to make this formula: ' clientreport = aggr(max(clientreport),clientID) ', since when I use max of the dimension then in all rows the max value disappears except the second-max one.

When the clientID is single row, it works tough.

You can see it using strings instead of values, as in ' =clientreport & '=' & aggr(max(clientreport),clientID) '

 

formula

clientreport = aggr(max(clientreport),clientID)

string

clientreport & '=' & aggr(max(clientreport),clientID)

[Digvijay_Singh]

Use nodistinct to show Max value in each row, like this - 

aggr(nodistinct max(clientreport),clientID)

[serpentium]

i cannot believe i lost two days working on it, and it was this simple.

you are lifesaver, thank you so much!!!!!!!!

[Digvijay_Singh · Accepted solution]

[Oleg_Troyansky]

Sorry I was late to this party... Just to add my 2 cents - in addition to the NODISTINCT, there are a few more things to keep in mind here:

- the AGGR() function returns an array of values, rather than a single value, which means that in certain situations, you may get a NULL as a result, because you would be comparing a single value with an array of values. In this particular case, since the AGGR returns one max value per clientID, which is also a dimension, it happens to work as expected.

- For a calculation like this, where you essentially need a total by one of the chart dimensions, I'd recommend simply using a TOTAL qualifier as a better option - it works more consistently, with less quirks, and it would perform a lot better than AGGR, which would be substantial on a large data set. So, the preferred formula would be:

clientreport = max( TOTAL <clientID> clientreport)

This is one of the examples that I teach at my lecture on Set Analysis and AGGR() at the Masters Summit for Qlik. We will be in Orlando and in Dublin this fall - check out our agenda and see if you can join. You will learn a lot of new advanced material from some of the best experts in the world!

[serpentium]

Max + total it's interesting idea, but doesn't work as dimension, works as measure, isn't it?

with dimension i can use a null() result and hide them