Sum of last 3 months sales in Load editor

[jvleensel]

Hi everyone,

the need is :

- Get a flatten market share by summing the sales of the 2 previous months + the current one and dividing this amount by the Total sales of these 3 months.

Example: Total sales for Jan, Feb and Mar is 100. For a product the sales in Jan is 1, in Feb 3 and in Mar 2. The expected MS should be (1+3+2)/100 = 6% of Market Share on month March (flatten).

Would you get ideas about how to achieve this?

Thank you and regards,

Jérémie

[felcar2013]

hi,

not a nice solution, but maybe useful. For better performance you would need probable another approach (in case your data set is large)

tmpTable:
LOAD RowNo() AS ID, * Inline [
Month,Sales,ProductSales
Jan,50,1
Feb,30,3
Mar,20,2
Apr,45,3
Mai,35,4
Jun,60,5
Jul,20,6
Aug,40,7
Sep,70,8
Oct,30,5
Nov,80,4
Dec,35,6
]
;

NoConcatenate
Table:
LOAD *,
num((ProductSales_3M/TotalSales_3M),'#,0%') AS %Market_Share
;

LOAD *,
RangeSum(Sales,Previous(Sales),Previous(Previous(Sales))) as TotalSales_3M,
RangeSum(ProductSales,Previous(ProductSales),Previous(Previous(ProductSales))) as ProductSales_3M
;
LOAD ID,
Month,
Sales,
ProductSales 
Resident tmpTable
Order by ID asc
;

DROP Table tmpTable;

[Kushal_Chawda]

Would you be able to share sample data?