Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a set of sales figures for the year, I want to be able to normalise them by dividing them by the price at a point in time (for example, January).
I am unable to get the Sales / Price figure to work for the full year when I have set the price figure to be for January. The issue is further complicated by needed it done at Site / Product level, so my original data has a "sum(aggr(sum" in it
Please see attached qvw.
This is a made up example as the data for the real example is sensitive and more complex but hopefully, a solution to this simple problem will help with my real world example.
Any thoughts / help welcome.
Thanks
Lisa, as mentioned - this is a made up example of a much more complicated issue I'm trying to resolve. The solution will hopefully have many applications including removing price fluctuations, remove the impact of currency on transactions etc. So I need to know if it is possible to normalise data as described, I don't really need to know that sales volumes of the data in the example.
I think I wouldn't try it within the UI because the aggr() could become quite complex and by larger datasets they are probably rather slow. This means I would calculate it within the script with:
t1:
load *, Site & '|' & Product as KEY, date(date#(Date_T, 'DD/MM/YYYY'), 'DD/MM/YYYY') as Date;
Load * Inline [
Site , Product , Date_T , Sales , Price
Site 1 , Product A , 01/01/2019 , 874 , 9.95
Site 1 , Product A , 01/02/2019 , 583 , 9.86
...
The date-stuff is just a quick adjustments to my local settings to ensure it's interpreted as date but the KEY is important to associate the table with the new princing-table, which is:
t2: load KEY, month(Date) as PriceMonth, avg(Price) as PriceAvg resident t1 group by KEY, month(Date);
And then you could use: =Sum(Sales) / avg(PriceAvg) as expression in your origin pivot and the month-switching happens over the selection from PriceMonth. It results in:
- Marcus