topic Re: Qlivkiew RangeCount with Set Analysis or similar in QlikView

Qlivkiew RangeCount with Set Analysis or similar

Mon, 07 Dec 2015 05:16:54 GMT

Hi Guys,

I am having a situation where i want to set the Result to "Yes" if the count of the Criteria 1 is "Yes" in the last consecutive 3 months and also have to group by ID

I have tried the following, but it seems the text in red can't work. Any suggestion, Gurus ?

RangeCount(above(Total Count({<[Criteria1]={'Y'}>}[Criteria1]),0,3))

Eg.

ID Date Criteria1 Result

1 1/1/15 Yes

1 1/2/15 Yes

1 1/3/15 Yes Yes

2 1/1/15 Yes

2 1/2/15 No

2 1/3/15 No No

Thx in Advanced , Guys

Re: Qlivkiew RangeCount with Set Analysis or similar

Mon, 07 Dec 2015 19:00:22 GMT

I use load script to get count of Criterial. Then I use those count to calculate the condition to get the indicator. Please see attachment.

Hope it help. Thanks.

Re: Qlivkiew RangeCount with Set Analysis or similar

Kushal_Chawda — Mon, 07 Dec 2015 19:15:18 GMT

try this

Data:

Load *, monthname(Date) as Month

inline [

ID, Date, Criteria1

1 , 1/1/15 , Yes

1 , 1/2/15 , Yes

1 , 1/3/15 , Yes

2 , 1/1/15 , Yes

2 , 1/2/15 , No

2 , 1/3/15 , No

];

left join(Data)

LOAD distinct ID,

Count(Month) as CntMonth

Resident Data

where lower(trim(Criteria1))='yes'

Group by ID;

left join(Data)

LOAD distinct ID,

'Yes' as Result,

max(Month) as Month

Resident Data

where CntMonth=3

Group by ID;

Re: Qlivkiew RangeCount with Set Analysis or similar

Tue, 08 Dec 2015 03:21:46 GMT

Thx a lot dust , It works perfectly

Re: Qlivkiew RangeCount with Set Analysis or similar

Tue, 08 Dec 2015 03:22:05 GMT

thx for your Kush