6 Replies Latest reply: Nov 10, 2016 11:40 AM by Luiz DePaula

# Previous day Stock on Hand - script

Hello experts,

I am having a challenge where I need to calculate the difference between the stock on hand from any day versus the stock on hand from previous day. So, another measure called "Previous Day Stock on Hand" has to be generated in the load script.

I thought about using the Peek function, but I could not come up with the right syntax.

I summary I would have

Day          Stock on Hand          Previous Day Stock on Hand

1                    300                                        -

2                    450                                        300

3                    400                                        450

4                    550                                        400

5                    350                                        550

This is what I need. Can anyone help me?

LD

• ###### Re: Previous day Stock on Hand - script

I don't see why another measure has to be created.  It can be done in the table on the front end, using above() function.

But if for some reason you still need another measure, you can do something like this:

Data:

Day,

"Stock On Hand"

FROM <some source>;

Day+1 as Day,

"Stock On Hand" as "Previous Day Stock on Hand"

RESIDENT Data;

• ###### Re: Previous day Stock on Hand - script

Hi Michael,

The logic works perfectly if I keep both tables separately and being linked by Date. However, if I try to left join, Qlik Sense just stays loading and does not finish. I created a separate table (Temp table) and in another syntax I left join, to finish loading two complete separate tables. But it does the same thing, it does not finish loading.

Any ideas?

Thanks,

LD

• ###### Re: Previous day Stock on Hand - script

I cannot guess what the problem is.  See attached, maybe it helps.

• ###### Re: Previous day Stock on Hand - script

Hello Michael,

Your logic actually worked perfectly. My load was getting stuck because the measure is based on 3 dimensions, being Date one of them. I was not including the other 2 dimension in my join load, which was causing the data set not to be linked properly. Once I realized that and added the other 2 dimensions, the load finished within seconds.

Thanks again!

LD

• ###### Re: Previous day Stock on Hand - script

If you peek you have to make sure that you read/sort the data in the right order.

Try

Day,

[Stock on hand],

peek([Stock on hand]) as [Previous Day Stock on Hand]

FROM Tablefile.qvd (qvd);

• ###### Re: Previous day Stock on Hand - script

Hi Vegar,

I was trying to use Peek as well, however peek only works from resident load and you have to add an Order by function to organize it. The function from Michael was simpler and worked perfectly.