Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Creator III

## 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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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>;

LEFT JOIN (Data) LOAD

Day+1 as Day,

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

RESIDENT Data;

6 Replies
Anonymous
Not applicable

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>;

LEFT JOIN (Data) LOAD

Day+1 as Day,

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

RESIDENT Data;

MVP

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);

Creator III
Author

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
Not applicable

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

Creator III
Author

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

Creator III
Author

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

Tags
Community Browser