xpiry date less than the line I am working on

[#1]

Hello

I am working on a stock analysis report.

I have different quantities different expiry dates.

I need to sum all quantities with expiry date less than the line I am working on.

Below is an example done on excel I need an expression to calculate the column “Cum Stock”:

ItemName	ExpDate	Stock	Cum Stock
Item A	2016-05-01	6	6
Item A	2017-01-01	194	200
Item A	2017-03-01	8	208
Item A	2017-03-17	10	218
Item A	2017-05-23	105	323

Thank You

[mato32188]

sum( {$<ExpDate = {'<(=GetFieldSelections(ExpDate))'}>} [Stock])

[#3]

Thank you for your answer but I am getting 0.

What the function "GetFieldSelections" does?

[mato32188]

It works with List Box with your field ExpDate, if you select any of ExpDate in it, it will sum your stock balance until ExpDate.

[mato32188]

sorry, forgot $ after '< ...'

[#6]

Actually what I want is that QlikView read the expiry date of the line I am in and some all quantities of same itexpiry date less then the one of the line I am in without the selection of any date.

Like in the example below:

200 = 194 +6

208=8+194+6

218=10+8+194+6

323=105+10+8+194+6

ItemName	ExpDate	Stock	Cum Stock
Item A	2016-05-01	6	6
Item A	2017-01-01	194	200
Item A	2017-03-01	8	208
Item A	2017-03-17	10	218
Item A	2017-05-23	105	323

[#7]

You can create a figure in the script that holds the cummulative total using the peek function:

numsum(Stock, peek( Stock_Accum)) as Stock_Accum

Note: You need to sort the data in Exp order first by using:

Order By ExpDate Asc;

[veidlburkhard]

Hi Marie-Anne,

please try this expression as accumulation formula:

RangeSum(above(TOTAL Sum(Stock),0,NoOfRows(TOTAL)))

This will give you the expected result.

Regards

Burkhard

[#9]

Thank you Burkhard Veidl  It works.

[#10]

Hi

The formula is working great when I select 1 item but if I have many items ;Rows 1,2,3 contains the same item but row 4 is a new item; I need the formula to restart the count for the new item.

For now the formula is adding all the above rows even if the item is different.

I need to sum when the item is the same, once I have a new item I need to restart the count.