topic Aggr with sum - something wrong in QlikView

Aggr with sum - something wrong

irmantas — Mon, 24 Sep 2018 06:16:43 GMT

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

ItemId	DatePhysical	Qty	TransType	TransRefId
JE-9011800	2018.07.03	3	2	gu074031
JE-9011800	2018.07.03	-3	2	gu074031
JE-9011800	2018.07.03	3	2	gu074031
PA-GR-KV-PASA-00-02	2018.07.03	-1,172	1	gu074031
PA-GR-KT-KUKU-00-03	2018.07.03	-0,752	1	gu074031
PA-GR-KT-KUKU-00-03	2018.07.03	-0,003	1	gu074031
PA-04-SAR-00-0-00-01	2018.07.03	-0,006	1	gu074031
PA-04-SAR-00-0-00-01	2018.07.03	-0,364	1	gu074031
PA-04-SAR-00-0-00-01	2018.07.03	-0,001	1	gu074031
PA-03-SAU-00-0-99-02	2018.07.03	-23,9	1	gu074031
PA-03-SAU-00-0-99-02	2018.07.03	-156,1	1	gu074031
PA-06-RUG-00-0-00-01	2018.07.03	-0,149	1	gu074031
PA-14-MIE-00-0-09-07	2018.07.03	-0,09	1	gu074031
PA-14-MIE-00-0-09-07	2018.07.03	-0,003	1	gu074031
PA-04-KOK-00-0-99-01	2018.07.03	-0,025	1	gu074031
PA-04-RIE-00-50-01	2018.07.03	-45	1	gu074031
PA-04-KAN-00-0-99-02	2018.07.03	-0,054	1	gu074031
PA-04-KAN-00-0-99-02	2018.07.03	-0,003	1	gu074031
PA-08-CIB-00-0-99-01	2018.07.03	-6	1	gu074031
PA-08-CIB-00-0-99-01	2018.07.03	6	1	gu074031
PA-12-NAC-00-0-50-01	2018.07.03	-0,015	1	gu074031
PA-GR-KT-ZIRN-00-03	2018.07.03	-0,15	1	gu074031
Z-GPS-PRIEM	2018.07.03	-3	1	gu074031
Z-GPS-SMULK	2018.07.03	-3	1	gu074031
Z-GPS-ATKRO	2018.07.03	-3	1	gu074031
Z-GPS-GRANU	2018.07.03	-3	1	gu074031

Re: Aggr with sum - something wrong

mayankraoka — Mon, 24 Sep 2018 07:02:18 GMT

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

Re: Aggr with sum - something wrong

andrey_krylov — Mon, 24 Sep 2018 07:27:37 GMT

Maybe without NODISTINCT

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

Re: Aggr with sum - something wrong

irmantas — Mon, 24 Sep 2018 07:34:41 GMT

The same - 9

Re: Aggr with sum - something wrong

irmantas — Mon, 24 Sep 2018 07:39:02 GMT

Without NODISTINCT in this case works, but I need NODISTINCT

Re: Aggr with sum - something wrong

andrey_krylov — Mon, 24 Sep 2018 07:51:20 GMT

Read this post about NODISTINCT in aggr

Re: What NODISTINCT parameter does in AGGR function?

Re: Aggr with sum - something wrong

mayankraoka — Mon, 24 Sep 2018 08:03:36 GMT

Remove nodistinct and try

Re: Aggr with sum - something wrong

irmantas — Mon, 24 Sep 2018 08:11:57 GMT

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

Re: Aggr with sum - something wrong

andrey_krylov — Mon, 24 Sep 2018 08:44:12 GMT

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

Re: Aggr with sum - something wrong

swuehl — Mon, 24 Sep 2018 09:28:45 GMT

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

Re: Aggr with sum - something wrong

irmantas — Mon, 24 Sep 2018 11:01:32 GMT

Maybe I must write all needs:

I have Invettrans table, were are all items transactions as you can see in my first post.

I need by selecting ItemId (TransType - "1") to find SUM of Item QTY, and in other field - SUM of all trans with TransType is "2" and TransRefId is the same as range by selected ItemId

Re: Aggr with sum - something wrong

irmantas — Mon, 24 Sep 2018 11:05:54 GMT

Finally, Thank you Stefan - working:)