Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor II

if missing value exists get previous value

Hello,

I have a table that some year month's values are missing. On hand = previous on hand + input - output 

but if there is missing value on hand value is calculated wrongly. First table calculation is wrong.

Second table on hand calculation is true. 

since in 202006 previous on hand (30) input (1) - output (2) = 29

Please help.

IDY Minputoutputon hand 
12020025830 
1202003--- 
1202004--- 
1202005--- 
120200612-1 
12020071090 
120200834-1 
      

 

 

IDY Minputoutputon hand
12020025830
1202003---
1202004---
1202005---
12020061229
120200710930
12020083429
1 Solution

Accepted Solutions
Highlighted
Valued Contributor II

Re: if missing value exists get previous value

It's working for me; see attached

you are clearly working on a different dataset as I can see date in between; maybe share your dashboard

View solution in original post

15 Replies
Highlighted
Partner
Partner

Re: if missing value exists get previous value

Hi

Try using Peek() or  Previous function.

Thanks

Highlighted
Contributor II

Re: if missing value exists get previous value

hello ,

It does not work . I tried.

Highlighted
Valued Contributor II

Re: if missing value exists get previous value

Peek works for me; see attached example with your data

PeekPreviousValue.png

Highlighted
Contributor II

Re: if missing value exists get previous value

thanks for your reply,

in data missing values are "-" instead of "". when I changed  into "-" , ıt didint work.

Highlighted
Valued Contributor II

Re: if missing value exists get previous value

see attached

Highlighted
Contributor II

Re: if missing value exists get previous value

thanks very much for fast reply. 

actually in my data missing values are not present . I mean , original data is 

IDY Minputoutputon hand
12020025830
12020061229
120200710930
12020083429

 

Highlighted
Valued Contributor II

Re: if missing value exists get previous value

Then I don't understand the problem. How do you fill in the gaps in "Y M"?

Highlighted
Contributor II

Re: if missing value exists get previous value

Let me aunderstand. 

Actually for each id I have 12 months, but some id's months are missing like example. there is no problem if there is all months. In missing months Problem occurs.  In below example, in 202006 on hand value must be 29 not -1.

IDY Minputoutputon hand
12020025830
12020061229
120200710930
12020083429
Highlighted
Valued Contributor II

Re: if missing value exists get previous value

A couple of questions:

- do you add the missing month in another table (e.g. calendar) or they are coming from other ID's?

- it's your plan to fill the missing months from a script perspective (i.e. generating the missing months and filling them in with the previous on hand value) or just in a chart (i.e. only when displaying the information)?