topic Re: FIFO logic in qlikview in QlikView

FIFO logic in qlikview

Thu, 05 Jun 2014 05:52:14 GMT

I have total net balance is 100.

I want to calculate quantity from latest date to the previous date till quantity 100.

How can I calculate quantity base on FIFO logic ?

eg.

date Quantity

01/01/2013 20

01/10/2013 40

01/30/2013 30

03/10/2013 50

10/10/2013 20

The calculation of quantity will starts from 10/10/2013 .

The required result as (20+50+30)=100

Re: FIFO logic in qlikview

MayilVahanan — Thu, 05 Jun 2014 06:01:24 GMT

Try like this

Test:

LOAD * INLINE [

date,Quantity

01/01/2013,20

01/10/2013,40

01/30/2013,30

03/10/2013,50

10/10/2013,20

];

Test1:

NoConcatenate

Load Quantity + Alt(Peek('Total'), 0) as Total, date Resident Test Order by date desc;

DROP Table Test;

Re: FIFO logic in qlikview

Thu, 05 Jun 2014 07:19:06 GMT

Could you let me know how your end result should look like?

Regards,

Prabhu

Re: FIFO logic in qlikview

Thu, 05 Jun 2014 08:54:12 GMT

Result look like as

date Quantity

01/30/2013 30

03/10/2013 50

10/10/2013 20

Total =100 quantity.

Re: Re: FIFO logic in qlikview

tresB — Thu, 05 Jun 2014 09:08:18 GMT

PFA

Update: By any chance, if you are using personal edition:

Test:

LOAD * INLINE [

date,Quantity

01/01/2013,20

01/10/2013,40

01/30/2013,30

03/10/2013,50

10/10/2013,20

];

Test1:

NoConcatenate

Load Quantity + Alt(Peek('Total'), 0) as Total, date, Quantity Resident Test Order by date desc;
Test2:
NoConcatenate
LOAD
*
Resident Test1 Where Total <=100;

Drop Table Test,Test1;

, too lazy to do my own when something is very close already there.

Re: FIFO logic in qlikview

Thu, 05 Jun 2014 11:02:15 GMT

Hi,

You can use this script

t1:

Load * Inline [

date, Quantity

01/01/2013, 20

01/10/2013, 40

01/30/2013, 30

03/10/2013, 50

10/10/2013, 20 ];

t2:

LOAD date,

Quantity,

Quantity+Alt(Peek('Total'),0) as Total

Resident t1

where Quantity+Alt(Peek('Total'),0) <= 100

Order by date desc;

Drop Table t1;

Regards

vivek

Load date,

Quantity

Resident t2

Order by date asc;

Drop table t2;

Re: FIFO logic in qlikview

MayilVahanan — Thu, 05 Jun 2014 11:08:15 GMT

Try like this

Test:

LOAD * INLINE [

date,Quantity

01/01/2013,20

01/10/2013,40

01/30/2013,30

03/10/2013,50

10/10/2013,20

];

Test1:

NoConcatenate

LOAD

* Where Total <=100;

Load Quantity + Alt(Peek('Total'), 0) as Total, date, Quantity Resident Test Order by date desc;

DROP Table Test;

Re: Re: FIFO logic in qlikview

CELAMBARASAN — Thu, 05 Jun 2014 11:40:08 GMT

If you want it in chart then try with dimension limits

Make sure that date is sorted in descending order

Use sum of rows in the expression tab instead of Expression total.You will get an output like above image

Re: FIFO logic in qlikview

ThornOfCrowns — Thu, 05 Jun 2014 11:57:27 GMT

Is that a Version 11 option only?

Re: FIFO logic in qlikview

tresB — Thu, 05 Jun 2014 12:01:07 GMT

Yes, V 11 onwards.