Aggr with Max problem - finding the highest different.

[yanivvl0]

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.

[sunny_talwar]

May be this:

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

[swuehl]

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

[Kushal_Chawda]

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

[Gysbert_Wassenaar]

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.

[yanivvl0]

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.

[Gysbert_Wassenaar · Accepted solution]

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

[yanivvl0]

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

[yanivvl0]

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

[yanivvl0]

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