You could use either one or two pivot tables. Here is how you make it with one:
I don't have your exact figures for Sales Share so they are based on Sales Growth that is obviously incorrect....
Sales Growth.qvf 176.0 K
Hi Petter. Thanks for your solution but I am stuck at one place. In my dataset there are multiple rows of same type of Package spread across different weeks. For instance:
500 ML, 584056
500 ML, 653000
500 ML, -453020
750 ML, 187309
750 ML, -200112
990 ML, 108838
Above 500 ML and 750 ML has multiple rows. In such a scenario, we need to compute the consolidated sales growth of 500 ML which is = 784036 (584056+653000-453020) and this being positive should come under Positive head.
Similarly, after computing for 750 ML, sales growth= -12803 (187309-200112), this should come under Negative head and likewise.
So, what changes should I make in my data load editor for the same?
Thanks in advance.
U can compute consolidated sales using
SUM(SalesGrowth) OVER (PARTITION BY Package) as TotalSum
functions in SQL (i always use this)
and then i prefer to make flagfield like
CASE WHEN (SUM(SalesGrowth) OVER (PARTITION BY Package))> 0 then 'Positive'
else 'Negative' end as FlagField
Then you can participate sum as you wish
Also for your information, in my dataset (with multiple entries of same package as below), I have following fields:
Package CurrentYear_Sales PrevYear_Sales
Sales Growth is computed as: (sum(CurrentYear_Sales)-sum(PrevYear_Sales))
Sales Share= sum(CurrentYear_Sales) / sum (Total (CurrentYear_Sales))
Please help me with the issue.