Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
irmantas
New 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
Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Aggr with sum - something wrong

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
Valued Contributor

Re: Aggr with sum - something wrong

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
Valued Contributor

Re: Aggr with sum - something wrong

Maybe without NODISTINCT

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

irmantas
New Contributor III

Re: Aggr with sum - something wrong

The same - 9

irmantas
New Contributor III

Re: Aggr with sum - something wrong

Without NODISTINCT in this case works, but I need NODISTINCT

andrey_krylov
Valued Contributor

Re: Aggr with sum - something wrong

Read this post about  NODISTINCT  in aggr

Re: What NODISTINCT parameter does in AGGR function?

mayankraoka
Valued Contributor

Re: Aggr with sum - something wrong

Remove nodistinct and try

irmantas
New Contributor III

Re: Aggr with sum - something wrong

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
Valued Contributor

Re: Aggr with sum - something wrong

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

MVP
MVP

Re: Aggr with sum - something wrong

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