Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
petteripaananen
Contributor
Contributor

DaysOfSupply calculation, how to calculate how long stock lasts?

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:

DateKEYStockDemand
201601012015
201601083010
201601155020
201601232010
201601303015
201602051020

The result should show

DateKEYDOS
201601011.5
201601082
201601153.67
201601231,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

3 Replies
sunny_talwar

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

petteripaananen
Contributor
Contributor
Author

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

Capture.PNG

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