topic Re: DaysOfSupply calculation, how to calculate how long stock lasts? in QlikView

DaysOfSupply calculation, how to calculate how long stock lasts?

Anonymous — Fri, 22 Apr 2016 08:44:29 GMT

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

Re: DaysOfSupply calculation, how to calculate how long stock lasts?

sunny_talwar — Fri, 22 Apr 2016 09:04:50 GMT

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

Re: DaysOfSupply calculation, how to calculate how long stock lasts?

Anonymous — Fri, 22 Apr 2016 09:07:32 GMT

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

Re: DaysOfSupply calculation, how to calculate how long stock lasts?

sunny_talwar — Fri, 22 Apr 2016 10:08:15 GMT

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