Create historic Stock Level Count

[#1]

Hi There QlikViewers

I am struggling to develop the expression I need to show my historic stock levels.  The fields I am working with are;

• Purchased Date
• Sales Date

I want to be able to display my historic stock levels by referencing the Purchased Date to the Sales Date.  So, in this example....

Item	Quantity	Purchased Date	Sales Date
Apples	100	01/01/2015	03/01/2015
Oranges	50	03/01/2015
Pears	25	02/01/2015
Apples	200	02/01/2015

...the stock levels by item quantity by date would be;

Date	Stock Level
01/01/2015	100
02/01/2015	325
03/01/2015	275
04/01/2015	275

I have been trying to work this out and have got completely stuck!  Any help appreciated!

[robert_mika]

Why do we have this line?

04/01/2015

275

[Gysbert_Wassenaar]

See attached example.

[#4]

Hi,

The line 04/01/2015 was just to show that the stock level would still be at 275 on that day because no transactions had taken place.

JW

[#5]

Hi There

Many thanks for your reply. 

This seems to work in your example qvw but I am not sure what the LEN(TRIM statement in the load script is doing with the date and what the RANGESUM(ABOVE expression is doing to the stock calculation. 

I've looked in the help menu but I have not understood. 

Please could you explain?

Thanks

JW

[robert_mika]

So if transaction is not taken place on 4,5,6... you still need to see this a a part of solution?

[#7]

Hi Robert

Yes, if no transactions have occured since 03/01/2015 I would want to see an entry for each day up to today. 

So today would show as 30/07/2015, 275.

JW

[Gysbert_Wassenaar]

The len(trim(..)) is to check if there is a value or not. The rangesum function is used in combination with the above() function to accumulate the stock amounts of each row.

[qlikviewwizard]

Hi Justine Willcox

Gysbert Wassenaar's solution is meets your requirement.

Can you attach the file with sample data you are using. So that it will help the folks to give faster solution.