Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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;
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;
Thanks a lot, it is the result that I'm looking for.
Hi Lironbaram,
If my data have a duplicate row with different sales figure, any idea how to resolve it?