Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
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
Try
=SUM(Aggr(sum(Distinct Price),Product,Store))
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.
Thank you for all of your help guys!
Happy holidays to you all!