Count ID based on field

[qlikwiz123]

I have ID, Date, MonthYear and Status as fields.

1. I have to count all the IDs that have more than one Running or Pending or Complete Status

2. I also have to count IDs that have more than one Running or Pending or Complete Status and fall within one year from the Date.

For problem 1,  I am using Count(DISTINCT {<ID = {"=Count({<Status = {'Running', 'Complete', 'Pending'}>} Status) > 1"}>} ID) but this also counts other ID rows with other Status values.

For problem 2, I am using Count(DISTINCT {<ID = {"=Count({<Status = {'Completed'},MonthYear ={">=$(=MonthName(addmonths(today(),-12)))"}>} Status) > 1"}>} ID) but this does not work either. The Set expression starts treating everything after 'MonthYear' as some kind of variable and gives errors.

I am attaching my QVW with some sample data. I have reached out before but couldn't really find the end solution for these 2 requirements. So reposting for bumping this question up. Any kind of help is highly appreciated.

[MayilVahanan]

HI 

Try like below

Count(DISTINCT {<Status = {'Running', 'Complete', 'Pending'}>} ID)

Count(DISTINCT {<Status = {'Complete'},Date ={">=$(=MonthStart(addmonths(today(),-12)))"}>}ID)

[yassinemhadhbi]

for problem 1

sum(if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>} ID),ID)>1,1,0))

For problem 2

sum(if(aggr(Count({<Status = {'Complete'},Date ={">=$(=MonthStart(addmonths(today(),-12)))"}>}ID),ID)>1,1,0))

[qlikwiz123]

Hi,

1 is failing

It is counting even though there is only one Complete and one Running. It should be Count if (Status=Complete or Running or Pending) occurs more than once per SID.

In simple terms, count SID if Count(Status=Running) >1 or Count(Status=Pending)>1 or Count(Status=Complete)>1

[yassinemhadhbi]

Can you please remove the Status from the dimension

[qlikwiz123]

Removed. Still see the count for ID A560591

[yassinemhadhbi]

Please , try this : 

sum(if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>}ID),ID,Status)>1,1,0))

[qlikwiz123]

Still does not work.

The ID is being counted twice here. It should be counted only once if it satisfies the conditon.

[yassinemhadhbi]

count(distinct if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>}ID),ID,Status)>1,1))

Just replace the sum with Count

[qlikwiz123]

Hi,

This seems to be working in a table with ID as dimension.

How do I show this number in a text box?