Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!!
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
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