Indicator using previous values

Mon, 17 Sep 2012 20:36:14 GMT

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!

Re: Indicator using previous values

rajni_batra — Tue, 18 Sep 2012 05:56:18 GMT

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

Re: Indicator using previous values

Wed, 19 Sep 2012 09:52:09 GMT

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

topic Re: Indicator using previous values in QlikView

Indicator using previous values

Re: Indicator using previous values

Re: Indicator using previous values