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: 
irmantas
Contributor III
Contributor III

Aggr with sum - something wrong

Hi,

sum(aggr(NODISTINCT sum({1<TransType = {2} >} QTY),TransRefId))

Working fine while only 1 line in range with the same TRANSTYPE + TransRefId, but if we have more than 1 line - something wrong.

In ex. is 3 lines with the same TransType and TransRefId and result of sum is 9??? must be 3

     

ItemIdDatePhysicalQtyTransTypeTransRefId
JE-90118002018.07.0332gu074031
JE-90118002018.07.03-32gu074031
JE-90118002018.07.0332gu074031
PA-GR-KV-PASA-00-022018.07.03-1,1721gu074031
PA-GR-KT-KUKU-00-032018.07.03-0,7521gu074031
PA-GR-KT-KUKU-00-032018.07.03-0,0031gu074031
PA-04-SAR-00-0-00-012018.07.03-0,0061gu074031
PA-04-SAR-00-0-00-012018.07.03-0,3641gu074031
PA-04-SAR-00-0-00-012018.07.03-0,0011gu074031
PA-03-SAU-00-0-99-022018.07.03-23,91gu074031
PA-03-SAU-00-0-99-022018.07.03-156,11gu074031
PA-06-RUG-00-0-00-012018.07.03-0,1491gu074031
PA-14-MIE-00-0-09-072018.07.03-0,091gu074031
PA-14-MIE-00-0-09-072018.07.03-0,0031gu074031
PA-04-KOK-00-0-99-012018.07.03-0,0251gu074031
PA-04-RIE-00-50-012018.07.03-451gu074031
PA-04-KAN-00-0-99-022018.07.03-0,0541gu074031
PA-04-KAN-00-0-99-022018.07.03-0,0031gu074031
PA-08-CIB-00-0-99-012018.07.03-61gu074031
PA-08-CIB-00-0-99-012018.07.0361gu074031
PA-12-NAC-00-0-50-012018.07.03-0,0151gu074031
PA-GR-KT-ZIRN-00-032018.07.03-0,151gu074031
Z-GPS-PRIEM2018.07.03-31gu074031
Z-GPS-SMULK2018.07.03-31gu074031
Z-GPS-ATKRO2018.07.03-31gu074031
Z-GPS-GRANU2018.07.03-31gu074031
1 Solution

Accepted Solutions
swuehl
MVP
MVP

You should be able to replace the NODISTINCT with Total<FIELDLIST> and adding dimensions to the Aggr() dimensions to get the needed granularity, and maybe the set expression in your inner aggregation is conflicting with your needs, but that's hard to tell without actually knowing your complete needs (you only told us 'I need NODISTINCT in other cases', but nothing about these other cases).

Maybe something like this:

sum( aggr( {1} sum(Total<TransRefId> If(TransType = 2, Qty)),TransRefId,ItemId))

View solution in original post

11 Replies
mayankraoka
Specialist
Specialist

Try this,

sum(total aggr(NODISTINCT sum(  {1<TransType = {2} >} QTY),TransRefId))

or better if you can attach screenshort of chart where you want this.

Regards,

Mayank

andrey_krylov
Specialist
Specialist

Maybe without NODISTINCT

sum(aggr(sum({1<TransType = {2} >} QTY),TransRefId))

irmantas
Contributor III
Contributor III
Author

The same - 9

irmantas
Contributor III
Contributor III
Author

Without NODISTINCT in this case works, but I need NODISTINCT

andrey_krylov
Specialist
Specialist

Read this post about  NODISTINCT  in aggr

Re: What NODISTINCT parameter does in AGGR function?

mayankraoka
Specialist
Specialist

Remove nodistinct and try

irmantas
Contributor III
Contributor III
Author

As I write, I need NODISTINCT in other case.

Just need to know , why the sum of qty is 9, because I have three rows, sum is made three times : 3 x 3 = 9

andrey_krylov
Specialist
Specialist

Because NODISTINCT replicates the sum to every (in this case all three) aggregated raw, then you sum them and get 9.

swuehl
MVP
MVP

You should be able to replace the NODISTINCT with Total<FIELDLIST> and adding dimensions to the Aggr() dimensions to get the needed granularity, and maybe the set expression in your inner aggregation is conflicting with your needs, but that's hard to tell without actually knowing your complete needs (you only told us 'I need NODISTINCT in other cases', but nothing about these other cases).

Maybe something like this:

sum( aggr( {1} sum(Total<TransRefId> If(TransType = 2, Qty)),TransRefId,ItemId))