topic Re: Sum, Set Analysis in QlikView

Sum, Set Analysis

Fri, 20 Dec 2013 20:40:11 GMT

Hi guys! I need some help.

Please refer to the table below as you answer my questions.

Product	Store	Price
A	1	5.00	SUP-1
A	1	5.00	SUP-2
A	2	7.00	SUP-1
A	2	7.00	SUP-2
B	1	10.00	SUP-1
B	1	10.00	SUP-2
B	2	9.50	SUP-1
B	2	9.50	SUP-2
C	1	21.00	SUP-1
C	1	21.00	SUP-2
C	2	19.50	SUP-1
C	2	19.50	SUP-2

I have a separate Object that shows the Total Price.

Using Set Analysis, how can I output the:

1. Total for a "selected" Product

For instance, Product A's Total Price is computed as 5.00 (Store 1 price) + 7.00 (Store 2 price) = 12.00

I only need one price of the product for each store.

2. Total Price of ALL products (if no specific product is selected)

Bearing in mind the above computation for a certain product, I need to get the Total Price for ALL products.

Product A (12.00) + Product B (19.50) + Product C (40.50) = 72.00

Thank you in advance!

Re: Sum, Set Analysis

Nicole-Smith — Fri, 20 Dec 2013 20:51:45 GMT

You should be able to use the following expression for both:

sum(aggr(only(Price),Store,Product))

If you select a product, it will only show the total for that selected product. If you don't select a product, it will show the total of all products.

**EDIT: See attached example.

Re: Sum, Set Analysis

hic — Fri, 20 Dec 2013 20:54:18 GMT

You don't need set analysis for this. If I understand you correctly, you want one price per distinct combination of Product and store, and you want to average within this group. Hence

Aggr(Avg(Price),Product,Store)

The Aggr() returns an array of Prices that you then want to sum. Hence your formula is

Sum(Aggr(Avg(Price),Product,Store))

If you use this in a chart with Product as dimension, the chart will display one value per product (answer to question 1) and the total will be the answer to question 2.

HIC

Re: Sum, Set Analysis

MK_QSL — Fri, 20 Dec 2013 20:54:50 GMT

Try

=SUM(Aggr(sum(Distinct Price),Product,Store))

Re: Sum, Set Analysis

Nicole-Smith — Fri, 20 Dec 2013 21:05:49 GMT

Henric Cronström's use of avg() inside of the aggr() is probably the best option in case you have different prices for the same product within the same store.

Re: Sum, Set Analysis

Fri, 20 Dec 2013 21:37:40 GMT

Thank you for all of your help guys!

Happy holidays to you all!