Normalising Data

[Karl_Hart]

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_P]

Why don' t you add another field to your table during the data load to calculate the No of products sold ?

[Karl_Hart]

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.

[marcus_sommer]

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