Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have the following dataset with month, year, sales unit forecast, stock quantity in the last day of the month, number of days in the month :
month,year,sales forecast,stock lastDay,days
1,2018,100,280,31
2,2018,80,200,28
3,2018,90,300,31
4,2018,110,250,30
5,2018,120,170,31
6,2018,100,200,30
7,2018,70,300,31
8,2018,80,250,31
9,2018,100,300,30
10,2018,110,300,31
11,2018,130,250,30
12,2018,160,200,31
];
I would like to evaluate the stock coverage per month using these rules:
stock coverage: how many days of stock I have with the sales forecast
ex:
jan = final stock = 280
sales forecast feb = 80 => 28 days
sales forecast mar = 90 => 31 days
sales forecast apr = 110 => 30 days
stock coverage january => 80+90+110 = 280 => 28+31+30 = 89 days
feb = final stock = 200
sales forecast apr = 110 => 30 days
stock coverage february => 90 + 110 = 200 => 31+30 = 61 days
mar = final stock = 300
sales forecast apr = 110 => 30 days
sales forecast may = 120 => 31 days
sales forecast jun = 100 => 30 days
stock coverage march => 110 + 120 = 230 => 300 - 230 = 70 => 30+31+ 30 * (70/100) = 30+31+21 = 82 days
I tried using dimension asOf and while via script but without success.
I would like a straight table with month, year and stock coverage dynamically (without load script)
Thank you so much in advance
a friend of mine got it using rangesum and below functions
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,1)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,1))/RangeSum(below(days,1,1))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,2)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,2))/RangeSum(below(days,1,2))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,3)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,3))/RangeSum(below(days,1,3))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,4)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,4))/RangeSum(below(days,1,4))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,5)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,5))/RangeSum(below(days,1,5))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,6)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,6))/RangeSum(below(days,1,6))) ,
'more than 6 months'))))))
a friend of mine got it using rangesum and below functions
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,1)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,1))/RangeSum(below(days,1,1))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,2)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,2))/RangeSum(below(days,1,2))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,3)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,3))/RangeSum(below(days,1,3))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,4)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,4))/RangeSum(below(days,1,4))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,5)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,5))/RangeSum(below(days,1,5))) ,
if(
sum([stock lastDay]) - RangeSum(below([sales forecast],1,6)) <= 0,
sum([stock lastDay]) / (RangeSum(below([sales forecast],1,6))/RangeSum(below(days,1,6))) ,
'more than 6 months'))))))