Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QSense
Creator II
Creator 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
Labels (4)
15 Replies
QSense
Creator II
Creator II
Author

1- I don't add missing months.

2- My purpose is whether there is 3 month or 12 month for each id , ı want to calculate on hand value based on previous on hand value.

In the below formula , when there is only one id , it works, but more than one ID it does not work

if (Row No() = 1 , [on hand],
if (
ID= previous(ID) and Row No() > 1,
peek([on hand],-1) + input- output,
If(
ID<> previous(ID) and Row No() = 1,[on hand],peek([on hand],-1) + input- output
)
)
)
as [on hand],

QSense
Creator II
Creator II
Author

In below case, It does not work

[
ID, Y M, input, output, on hand
1, 202002,5, 8, 30
1, 202003,-,-,
1, 202004,-,-,
1, 202005,-,-,
1, 202006,1, 2, -1
1, 202007,10, 9, 0
1, 202008,3, 4, -1
2, 202004,10,5,25
2, 202005,3,7,-4
2, 202006,1, 3, -2
2, 202007,10, 9, 1
2, 202008,3, 4, -1
]

lorenzoconforti
Specialist II
Specialist II

Try this:

if (RowNo() = 1 , [on hand],
if (ID = previous(ID) and RowNo() > 1,
peek([on hand], -1) + input- output,
If(ID <> previous(ID),[on hand],peek([on hand],-1) + input- output
)
)
)
as [on hand],

QSense
Creator II
Creator II
Author

 

 

as you see, it works for only one Id

 

peek.PNG

 

 

 

 

 

 

lorenzoconforti
Specialist II
Specialist II

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

QSense
Creator II
Creator II
Author

Thank you . It also worked for me