topic Re: Create historic Stock Level Count in QlikView

Create historic Stock Level Count

Thu, 30 Jul 2015 08:13:12 GMT

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!

Re: Create historic Stock Level Count

robert_mika — Thu, 30 Jul 2015 09:09:12 GMT

Why do we have this line?

04/01/2015

275

Re: Create historic Stock Level Count

Gysbert_Wassenaar — Thu, 30 Jul 2015 09:25:00 GMT

See attached example.

Re: Create historic Stock Level Count

Thu, 30 Jul 2015 09:55:32 GMT

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.

Re: Create historic Stock Level Count

Thu, 30 Jul 2015 10:01:40 GMT

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

Re: Create historic Stock Level Count

robert_mika — Thu, 30 Jul 2015 10:01:44 GMT

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

Re: Create historic Stock Level Count

Thu, 30 Jul 2015 10:04:33 GMT

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.

Re: Create historic Stock Level Count

Gysbert_Wassenaar — Thu, 30 Jul 2015 10:14:00 GMT

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.

Re: Create historic Stock Level Count

qlikviewwizard — Fri, 31 Jul 2015 03:05:16 GMT

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.