Previous day Stock on Hand - script

[luizcdepaula]

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?

Thanks in advance.

LD

[Anonymous · Accepted solution]

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:

LOAD

  Day,

  "Stock On Hand"

FROM <some source>;

LEFT JOIN (Data) LOAD

  Day+1 as Day,

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

RESIDENT Data;

[Vegar]

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

Try

LOAD

Day,

[Stock on hand],

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

FROM Tablefile.qvd (qvd);

[luizcdepaula]

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

[Anonymous]

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

[luizcdepaula]

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

[luizcdepaula]

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.

Thanks for your suggestion.

LD