Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

luizcdepaula
Contributor II

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?

Thanks in advance.

LD

Tags (2)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

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:

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;

6 Replies
mov
Esteemed Contributor III

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:

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
Valued Contributor III

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

LOAD

Day,

[Stock on hand],

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

FROM Tablefile.qvd (qvd);

Please ekskuse my Norglish and Swenglish typos.
luizcdepaula
Contributor II

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

mov
Esteemed Contributor III

Re: Previous day Stock on Hand - script

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

luizcdepaula
Contributor II

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

luizcdepaula
Contributor II

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.

Thanks for your suggestion.

LD