Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

aggr sum and deduct above values for a field

Requirement is : 

For the given set of data for all the null valued cwip id : value1 should be the current value minus the sum of value for the above cwip ids, i.e., for 250261 value 1 would be  59398663 - (15646800+43751863) = 0 , for 307184 value1  would be 10891678 - (10806838) = 84840. Refer ATTACHMENT.

asset id cwip id                  value 1                 value2                                  

250261  280263                 43751863.38      43751863.38      

250261  280255                 15646800            15646800            

250261  NULL                     59398663.38      0                             

307142  404231                 4817181.91         4817181.91         

307142  NULL                    4817181.91         0                             

307184  404295                10806837.5         10806837.5         

307184  NULL                    10891677.5         0                             

Labels (1)
  • above

1 Solution

Accepted Solutions
ashok_rajm
Contributor III
Contributor III

Try Below syntax. This could help.

if([cwip id]='NULL',
    sum([value 1])-Sum(aggr(nodistinct sum({<[cwip id] -= {'NULL'}>}[value 1]),[asset id])),
       sum([value 1]))

View solution in original post

1 Reply
ashok_rajm
Contributor III
Contributor III

Try Below syntax. This could help.

if([cwip id]='NULL',
    sum([value 1])-Sum(aggr(nodistinct sum({<[cwip id] -= {'NULL'}>}[value 1]),[asset id])),
       sum([value 1]))