Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I have the following issue: I want to calculate the share of Sales of Articles included in a promotion as a % of the total sales of ALL articles. As an example I have this:
The raw data is like this:
Article | Week | Sales | Promo (y/n) |
---|---|---|---|
a | 1 | 10 | y |
b | 1 | 10 | y |
c | 1 | 10 | n |
a | 2 | 20 | y |
b | 2 | 10 | y |
c | 2 | 20 | n |
I am trying to buid a pivot like this:
Only show the elements of Article where Promo = 'y'
Week | Article in Promo | Sales | Total Sales | % of Total Sales |
---|---|---|---|---|
1 | a | 10 | 30 | 33% |
b | 10 | 30 | 33% | |
2 | a | 20 | 50 | 40% |
b | 10 | 50 | 20% |
For the calculation of the Total Sales I have been trying the following
1. When I use:
sum( { < Promo = > } TOTAL Sales)
I get 80 in each row, which is the grand Total of all Articles, but I want it to be by week.
2. When I use:
sum( { < Promo = > } TOTAL < Week > Sales)
I get 20 for Week 1 (instead of 30) and 30 for Week 2 (instead of 50)
Now I get the sum by week but not for all articles, but only for the ones shown in the table.
Is there a way of getting the total sum for all articles by week, as in the table above?
Would appreciate your ideas very much
Thanks
Alejandro
Hi,
Have a look at the application attached.
Regards,
Kaushik Solanki
Thanks Kaushik,
the thing is that it works as long as the article "c" is shown in the rows. Is there a way to show only a and b and still have the Total Value from a, b and c in the Total Sales column?
Regards
Alejandro
Total sales:
sum(aggr(sum({1}<Week>TOTAL Sales),Week,[Article in Promo]))
The Total will change if you make selections so for every selection you want to be reflected in the total you add the FIELD=p(FIELD) as in:
sum(aggr(sum({1}<Week>TOTAL Sales),Week,[Article in Promo])) Hope it helps!