Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

if missing value exists get previous value

Hello ,

I have a problem. I have a straight table and column names are 

Month, ID, INPUT, OUTPUT, ONHAND

Onhand formula is : peek(Onhand,-1) + input - output.

As you see there is no 202007 value so 202008 onhand value is wrong.

It must be 2 then after 202008 value's must be according to formula . In other words if there is missing month previous month' onhand value must be taken  then calculation  must go on;

202009      0

202010      -3

202011      -5

202012      5

202101      -2

 

nth row.PNG

 

Labels (3)
3 Replies
Highlighted
Partner
Partner

Hello,

Write a if condition if prev month doesnt exist then use previous value of Onhand column else calculate it.

Regards,

Shivani Sapkale

Highlighted
Creator II
Creator II

Hello,

I wrote below if condition but does not work

 if (
 RowNo() = 1 , ONHAND,
 if (
RowNo()>1 and ID= Peek('ID') and Month - Peek('Month ') > 31,peek(ONHAND,-1),
if (
RowNo()>1
and ID = Peek('ID') and Month- Peek('Month') < 32, (peek(ONHAND,-1) + INPUT - OUTPUT)
 ))) as ONHAND_CAL_0

 

Highlighted
Partner
Partner

Hi,

Kindly find the attached QVF.

Hope it helps.

Thanks & Regards