topic Re: Aggr with Max problem - finding the highest different. in QlikView

Aggr with Max problem - finding the highest different.

yanivvl0 — Tue, 22 Mar 2016 09:52:35 GMT

hi to all ,

i have an expresion :

= sum ( aggr ( max ( {$<Subject={1}>} wanted_num ) ,ID ) ) - sum ( aggr ( max ( {$<Subject={1}>} current_num ) ,ID ) )

shortly - i need to find the sums diff between wanted_num and current_num for each ID.

Its OK , but i need also to find the max result of the highest ID , so i use

= aggr ( max

( sum ( aggr ( max ( {$<Subject={1}>} wanted_num ) ,ID ) ) - sum ( aggr ( max ( {$<Subject={1}>} current_num ) ,ID ) ) ) , ID)

But it does not work ,

NEED YOUR HELP , THANKS.

Re: Aggr with Max problem - finding the highest different.

sunny_talwar — Tue, 22 Mar 2016 09:56:10 GMT

May be this:

FirstSortedValue({<Subject = {1}>} wanted_num, -ID) - FirstSortedValue({<Subject = {1}>} current_num, -ID)

Re: Aggr with Max problem - finding the highest different.

swuehl — Tue, 22 Mar 2016 10:23:35 GMT

I don't understand what you are trying to achieve, could you explain with some sample lines of records?

This

= sum ( aggr ( max ( {$<Subject={1}>} wanted_num ) ,ID ) ) - sum ( aggr ( max ( {$<Subject={1}>} current_num ) ,ID ) )

does not 'sums diff between wanted_num and current_num for each ID', it calculates the difference between the sums across each id. Which makes a difference, IMHO...

Re: Aggr with Max problem - finding the highest different.

Kushal_Chawda — Tue, 22 Mar 2016 10:35:34 GMT

try this,

=FirstSortedValue (sum ( aggr ( max ( {$<Subject={1}>} wanted_num ) ,ID ) ) - sum ( aggr ( max ( {$<Subject={1}>} current_num ) ,ID ) ) ,

- aggr(sum ( aggr ( max ( {$<Subject={1}>} wanted_num ) ,ID ) ) - sum ( aggr ( max ( {$<Subject={1}>} current_num ) ,ID )) ,ID ) )

Re: Aggr with Max problem - finding the highest different.

Gysbert_Wassenaar — Tue, 22 Mar 2016 11:52:00 GMT

Please post a small qlikview document with some example data and a table with the results you expect. An excel file with source data and a result table are ok too.

Re: Aggr with Max problem - finding the highest different.

yanivvl0 — Wed, 23 Mar 2016 08:14:29 GMT

HI Gysbert ( and all the rest minds ) ,

You're right, we should see a concrete example, I have attached a document. the idea is to paint the DIFF column in shades of green, in relative to the that value appears.

So if the formula = sum ( aggr ( max ( {$<Subject={1}>} (WANTED_NUM-CURRENT_NUM) ) , ID ) )

i am using variable vColorDiff = 255 / max ( sum ( aggr ( max ( {$<Subject={1}>} (WANTED_NUM-CURRENT_NUM) ) , ID ) ) )

and in the background color = argb ( sum ( aggr ( max ( {$<Subject={1}>} (WANTED_NUM-CURRENT_NUM) ) , ID ) ) * vColorDiff , 0 , 140 , 0 ) -So a high value will be dark

the problem is that vColorDiff expression is error ..

THANKS.

Re: Aggr with Max problem - finding the highest different.

Gysbert_Wassenaar — Wed, 23 Mar 2016 08:39:41 GMT

You were nesting aggregation functions without an aggr. You could also try using the ColorMix wizard. See attached qvw

Re: Aggr with Max problem - finding the highest different.

yanivvl0 — Wed, 23 Mar 2016 09:42:17 GMT

Great solution !! you are making a big help , thanks!!!!!!!

Re: Aggr with Max problem - finding the highest different.

yanivvl0 — Wed, 23 Mar 2016 09:43:59 GMT

hi , i try to use it - but something is wrong , any way i got help from the great Gysbert

Re: Aggr with Max problem - finding the highest different.

yanivvl0 — Wed, 23 Mar 2016 09:45:22 GMT

Hi its not good , i think i didnt make the question clear , hi , i try to use it - but something is wrong , any way i got help from the great Gysbert

Re: Aggr with Max problem - finding the highest different.

sunny_talwar — Wed, 23 Mar 2016 09:46:28 GMT

Yup, I saw that below. I am glad you got what you were looking for