total sum per product QLIK SENSE

[Modiz]

Hi,

I have a simple table in which I have four columns: product, date, quantity, store.  I would like to make a total sum by product,

I used this AGGR(sum({1<product=>} quantity), product) but I have the result only in one row.

I also tried the second option AGGR(NODISTINCT SUM(quantity),product): but after setting the filter to the store, the quantity in this column changes.

I would like to get a result like this:

product	date	quantity	store	total_quantity
a	01.01	5	s_1	17
a	02.01	10	s_1	17
a	02.01	2	s_2	17
b	01.01	1	s_1	5
b	02.01	2	s_1	5
b	02.01	3	s_2	5

and if i use filter the table will look like this:

product	date	quantity	store	total_quantity
a	01.01	5	s_1	17
a	02.01	10	s_1	17
b	01.01	1	s_1	5
b	02.01	2	s_1	5

Can anyone help me?

[Oleg_Troyansky]

Hi Domi,

Your confusion about the AGGR() function is very common... In order to understand what's going on there, join my on-line session for Set Analysis and AGGR() on March 1st. I will be going over exactly this scenario, and I'll cover many other advanced uses of Set Analysis and AGGR(). Details and registration can be found here: Masters Summit for Qlik. 

In your specific case you don't need AGGR though. A simple TOTAL qualifier would be better:

SUM(TOTAL <product> quantity)

Cheers,

[sidhiq91]

@Modiz Please see the below simple  expression.

Sum(total<product> quantity)

See the scree shot below:

If this resolves your issue,please like and accept it as a solution.

[Modiz]

thank you for your answers but this solution doesn't work if i use filter on column "store" 😞

[Modiz]

I guess I found a solution, maybe someone will find it useful. Below is the set expression I used:

=AGGR(NODISTINCT Sum({1<product=>} quantity),product)

[Oleg_Troyansky · Accepted solution]

Hi Domi,

Two comments:

1. If you wanted to disregard user selections, the best way of doing it would be adding Set Analysis {1} to the sum with TOTAL:

SUM({1} TOTAL <product> quantity)

I claim that this calculation produces more reliable and faster results that the same calculation done with AGGR. I'm explaining it in detail in my session on Set Analysis (March 1st!)

2. After Set ID {1},  any filters that disregard selections in individual fields are redundant. Set Analysis {1} gives you the whole document with no selections anyway. So, if you insist on using AGGR and not TOTAL, your formula should look like this:

=AGGR({1} NODISTINCT Sum(quantity),product)

But test your results carefully. NODISTINCT may cause duplication of your results, depending on your data structure.

Best,