topic Count ID based on field in QlikView

Count ID based on field

qlikwiz123 — Mon, 14 Jun 2021 23:52:36 GMT

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.

Re: Count ID based on field

MayilVahanan — Tue, 15 Jun 2021 08:50:13 GMT

Try like below

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

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

Re: Count ID based on field

yassinemhadhbi — Tue, 15 Jun 2021 10:49:21 GMT

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))

Re: Count ID based on field

qlikwiz123 — Tue, 15 Jun 2021 14:39:56 GMT

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

Re: Count ID based on field

yassinemhadhbi — Tue, 15 Jun 2021 14:42:26 GMT

Can you please remove the Status from the dimension

Re: Count ID based on field

qlikwiz123 — Tue, 15 Jun 2021 14:49:06 GMT

Removed. Still see the count for ID A560591

Re: Count ID based on field

yassinemhadhbi — Tue, 15 Jun 2021 15:00:54 GMT

Please , try this :

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

Re: Count ID based on field

qlikwiz123 — Tue, 15 Jun 2021 15:08:04 GMT

Still does not work.

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

Re: Count ID based on field

yassinemhadhbi — Tue, 15 Jun 2021 15:15:17 GMT

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

Just replace the sum with Count

Re: Count ID based on field

qlikwiz123 — Tue, 15 Jun 2021 15:25:51 GMT

Hi,

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

How do I show this number in a text box?

Re: Count ID based on field

yassinemhadhbi — Tue, 15 Jun 2021 15:40:08 GMT

Try this:

=sum(

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

Re: Count ID based on field

qlikwiz123 — Tue, 22 Jun 2021 18:44:30 GMT

@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)