FIFO logic in qlikview

[#1]

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

[MayilVahanan]

Hi

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;

[#3]

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

Regards,

Prabhu

[#4]

Result look like as

  date                  Quantity

01/30/2013                30

03/10/2013                50

10/10/2013                20

Total =100 quantity.

[tresesco]

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.

[#6]

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; 

[MayilVahanan]

Hi

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;

[CELAMBARASAN]

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

[ThornOfCrowns]

Is that a Version 11 option only?

[tresesco]

Yes, V 11 onwards.