Accumulative Average Margin % Scripting

[cjliew]

Dear All, 

Any idea what scripting we can write in order to get the result of the "Avg Margin" as below?

The "Avg Margin" is calculated based on the accumulative of Margin divide by accumulative of Sales.

So far, I have tried the following script, but the return result is by year, instead of period:

[lironbaram · Accepted solution]

hi 

assuming you loaded the data field to table named Table1 
this script should give you the accumulated Sales and cost for each date so you can calculate the margin

Table2: 
load *,
     if(Previous(company)=company, rangesum(peek('accSales'),Sales),Sales) as accSales,
 if(Previous(company)=company, rangesum(peek('accCost'),Cost),Cost) as accCost
Resident Table1
Order By Company,Year,Period; 
drop Table1;

[cjliew]

Thanks a lot, it is the result that I'm looking for.

[cjliew]

Hi Lironbaram, 

If my data have a duplicate row with different sales figure, any idea how to resolve it?