Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Indicator using previous values

Hi there.  I am struggling with something that I think is quite basic but cannot figure out.  I have had a look at the previous and peek functions, with no success.  What I would like is to create a new field called fmonth that looks at PurchGT0 and is like an ever indicator - i.e. if it becomes 1, it stays 1 for the rest of that ID value and then resets itself at the start of the next ID value.  The size of the data could change depending on the number of ID's and the number of months.

TTable:

LOAD * INLINE [

    ID, Month, Purch, PurchGT0

1, 201201, 100, 1

1, 201202, 100, 1

1, 201203, 0, 0

1, 201204, 100, 1

1, 201205, 150, 1

1, 201206, 300, 1

2, 201201, 0, 0

2, 201202, 0, 0

2, 201203, 600, 1

2, 201204, 0, 0

2, 201205, 0, 0

2, 201206, 100, 1

];

Desired outcome:

//ID, Month, Purch, PurchGT0, fmonth

//1, 201201, 100, 1,1

//1, 201202, 100, 1,1

//1, 201203, 0, 0,1

//1, 201204, 100, 1,1

//1, 201205, 150, 1,1

//1, 201206, 300, 1,1

//2, 201201, 0, 0,0

//2, 201202, 0, 0,0

//2, 201203, 600, 1,1

//2, 201204, 0, 0,1

//2, 201205, 0, 0,1

//2, 201206, 100, 1,1

I'm stumped so any help would be massively appreciated!

2 Replies
rajni_batra
Specialist
Specialist

use if(isnull(PurchGT0),peek(PurchGT0),PurchGT0) as fmonth

Not applicable
Author

Thanks Rajni but I end up with fmonths exactly the same as PurchGT0?