Aggr with sum - something wrong

[irmantas]

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

[mayankraoka]

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]

Maybe without NODISTINCT

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

[irmantas]

The same - 9

[irmantas]

Without NODISTINCT in this case works, but I need NODISTINCT

[andrey_krylov]

Read this post about  NODISTINCT  in aggr

Re: What NODISTINCT parameter does in AGGR function?

[mayankraoka]

Remove nodistinct and try

[irmantas]

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]

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

[swuehl · Accepted solution]

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