DaysOfSupply calculation, how to calculate how long stock lasts?

[Anonymous]

Hello everybody,

I need to calculate days of supply daily ie. how long the daily stock would last if no new stuff is coming in. The date is a first day of the week always:

The data:

DateKEY	Stock	Demand
20160101	20	15
20160108	30	10
20160115	50	20
20160123	20	10
20160130	30	15
20160205	10	20

The result should show

DateKEY	DOS
20160101	1.5
20160108	2
20160115	3.67
20160123	1,67

So the full week and then a portion of the not full following week.

The raw data actually has more than one value for each week; I get the stock and demand as Sum({<$(vStock)>} Kg) and Sum({<$(vDemand)>} Kg)

The formula I was working on is the following, there is some issues with it:

Max(

  Aggr(

  If(

        Rangesum(

          Above(

            Sum({<$(Demand)>} Kg)

            ,0,RowNo()

          )

        )

        <=

        Sum({<$(vStock)>} Kg)

        , DateKEY) // if

    , DateKEY)// aggr

        )//max

I really appreciate your help!

Cheers, Petteri

[sunny_talwar]

How are you calculating the numbers in the DOS column of your output?

[Anonymous]

For 20160101:

First full weeks:

1 -> 5 is left for next week

then 5 / 10 (next weeks demand) -> 0.5

=> 1 + 0.5 = 1.5

Cheers, Petteri

[sunny_talwar]

I got a slightly different number for 20160115

Expression:

If(Below(Stock-Demand) > 0, (Stock-Demand)/Below(Demand)) + 1

I guess this will translate into this for you

If(Below(Sum({<$(vStock)>} Kg)-Sum({<$(vDemand)>} Kg)) > 0, (Sum({<$(vStock)>} Kg)-Sum({<$(vDemand)>} Kg))/Below(Sum({<$(vDemand)>} Kg))) + 1