topic Cannot compare max value of a dimension and the inline value of the dimension in App Development

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

serpentium — Thu, 24 Aug 2023 14:42:05 GMT

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)

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

Digvijay_Singh — Thu, 24 Aug 2023 14:59:36 GMT

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

aggr(nodistinct max(clientreport),clientID)

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

serpentium — Thu, 24 Aug 2023 15:01:11 GMT

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

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

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

Digvijay_Singh — Thu, 24 Aug 2023 15:04:04 GMT

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

Oleg_Troyansky — Thu, 24 Aug 2023 18:13:05 GMT

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!

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

serpentium — Thu, 24 Aug 2023 18:44:08 GMT

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