Qlivkiew RangeCount with Set Analysis or similar

[#1]

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

[#2 · Accepted solution]

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.

[Kushal_Chawda]

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;

[#4]

Thx a lot dust , It works perfectly

[#5]

thx for your Kush