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.

[yassinemhadhbi · Accepted solution]

Try this: 

=sum(

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

[qlikwiz123]

@yassinemhadhbi 

Hi, really appreciate your help

Unfortunately, I discovered an issue with the expressions you have given expressions.

Count(DISTINCT {<ID = {"=Count({<Status = {'Complete'},[Type]={'Primary'}>} Status) > 1"},
Status={'Complete'},MonthYear ={">=$(=MonthName(addmonths(today(),-12)))"}>} DISTINCT ID)

This is supposed to count IDs if they have more than one 'Complete' status, Type as 'Primary' and MonthYear is within rollign 12 months (which means anything from June 2020 to June 2021). But this is also counting IDs in May 2020. I think it is because we are not doing MonthYear check in the 2nd count expression. 

When I add it as below, it shows as invalid expression

Count(DISTINCT {<ID = {"=Count({<Status = {'Complete'},[Type]={'Primary'},MonthYear ={">=$(=MonthName(addmonths(today(),-12)))"}>} Status) > 1"},
Status={'Complete'},MonthYear ={">=$(=MonthName(addmonths(today(),-12)))"}>} DISTINCT ID)