Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
sum( {$<ExpDate = {'<(=GetFieldSelections(ExpDate))'}>} [Stock])
Thank you for your answer but I am getting 0.
What the function "GetFieldSelections" does?
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.
sorry, forgot $ after '< ...'
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 |
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;
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
Thank you Burkhard Veidl It works.
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.