topic Puzzle in Stock Management in QlikView

Puzzle in Stock Management

Tue, 13 Nov 2012 14:25:13 GMT

Hello,

I'm developing an application for Stock Management, with some basic information (Date, Material, Inflow, Ouflow, Stock)

I need the following KPI : if there were NO inflow, how long would the stock cover the outflows (we should get one value for each date).

Let's take an example :

Material     Date               Outflow     Inflow     Stock
A                01.01.2012      10           15          50
A                02.01.2012      30           20          40
A                03.01.2012      10           5            35
A                04.01.2012      15           10          30
A                05.01.2012      10           5            25
A                06.01.2012      30           15          10
A                07.01.2012      25           10          -5
A                08.01.2012      20           20          -5

For 01.01.2012,

     Start stock is 50
     On 02.02.2012, outflow is 30, then remaining stock is 50-30=20 (Remaining stock covers 100% of the inflows)
     On 03.02.2012, outflow is 10, then remaining stock is 20-10=10 (Remaining stock covers 100% of the inflows)
     On 04.02.2012, outflow is 15, which is bigger than the remaining stock (So, the remaining stock covers 10/15 = 66% of the outflows on that day)

Result : For 01.01.2012, the KPI is 2.66 (the original stock is covering 2.66 days outflows)

For 02.02.2012,

     Start stock is 40
     On 03.02.2012, outflow is 5, then remaining stock is 40-5=35
     On 04.02.2012, outflow is 10, then remaining stock is 35-10=25
     On 05.02.2012, outflow is 5, then remaining stock is 25-5=20
     On 06.02.2012, outflow is 15, then remaining stock is 20-15=5
     On 07.02.2012, outflow is 10, which is bigger than the remaining stock (5/10 = 0.5)

Result : For 02.01.2012, the KPI is 4.5 (the original stock is covering 4.5 days outflows)

And so on for each available date...

The question is : is it possible to calculate it in QlikView ? (I'm sure it is, although I did not find yet )

In the full version of this problem, there will be other dimensions. It means that it is not possible to precalculate any KPI in the script, because KPI depends on the selection.

My feeling is that it will be necessary to create an isolated data island and some aggr function, but every try fails...

Do you know how to do that ?

I thank you in advance if you can help me !!!

Re: Puzzle in Stock Management

Tue, 13 Nov 2012 15:06:51 GMT

Hi Pierre,

in qlikview u want to calculate field "stock". it is possible in qlikview and

one thing i cant understand plz explain me what is the formulae for calculate 'Result' Field

Ananth

Re: Puzzle in Stock Management

Tue, 13 Nov 2012 16:55:14 GMT

Hi Ananth,

Thanks for looking to this problem

I already have the field 'stock' (it is basically the previous stock value + inflow - outflow). There is no issue with it.

The KPI I'm looking for is :

For each date, there is a stock. In the following days, there will be outflows. Then, I want to know how long the stock will cover my outflows (we neglegt inflows in this calculation).

I gave two examples, let's take one of them again :

On 01.01.2012, the stock is 50

The following ouflows are : 30 (02.01.2012), 10 (03.01.2012) and 15 (04.01.2012)

After one day, the stock would be 20 (50-30)

After two days, the stock would be 10 (50-30-10)

On the third day, the remaining stock is not big enough to cover my outflows (because the outflows are 30+10+15=55).

The KPI is an amount of days : 1 (all outflows from 1st day are covered) + 1 (all outflows from 2nd day are covered) + 10/15 (only 10 outflows are covered out of 15) = 2,66

I hope it helps for a better understanding of this problem

Thanks