Stock Coverage with forecast sales

[igorgois_]

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 :

load * inline [

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 mar = 90 => 31 days

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

[igorgois_ · Accepted solution]

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'))))))