Count ID based on field

[qlikwiz123]

I have two Columns - ID and Status

ID

1

2

3

4

..

Status

Running

Pending

Complete

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

[sunny_talwar]

May be this

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

[qlikwiz123]

Hi @sunny_talwar  Thank you. This works. And how do I show IDs that only have value >=1 in a chart?

When I add ID as dimension and this expression, it shows IDs even with Expression values with 0 in it. I tried 

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

This gives me error in Dimension

[sunny_talwar]

Try this with Aggr()

Aggr(If(Count(DISTINCT {<ID = {"=Count({<Status = {'Running', 'Complete', 'Pending'}>} Status) > 1"}>} ID)>=1, ID, Null()), ID)

[qlikwiz123]

How do I add

MonthYear={">=$(=MonthName(addmonths(today(),-12)))"} in the above Set Analysis? It is giving me errors when I do 

Count(DISTINCT {<ID = {"=Count({<Status = {'Running', 'Complete', 'Pending'},MonthYear ={">=$(=MonthName(addmonths(today(),-12)))"}>} Status) > 1"}>} ID)

[sunny_talwar]

How is MonthYear field created in the script? Do you use MonthName function to create it? or do you use Date() function with some formatting?

[qlikwiz123]

I am doing this in the script using Date() and MonthName, to see if at least one works.

Script:

date(MonthStart(date#(Date(Date#([DateField],'YYYYMMDD'),'MM/DD/YYYY'),'MM/DD/YYYY')),'MMM YYYY') as MonthYear

MonthName(date(MonthStart(date#(Date(Date#([DateField],'YYYYMMDD'),'MM/DD/YYYY'),'MM/DD/YYYY')),'MM/DD/YYYY')) as MonthYear1

Expression:

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

Error:

It just converts everything after '$' into some variable thing and gives error.

[qlikwiz123]

@sunny_talwar 

I have attached my sample data QVW for easy understanding.

Looks like the original expression 

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

is also failing. It is also counting other values in 'Status' and adding them. My requirement is to count ID if there is more than one 'Running' or 'Complete' or 'Pending' status value for that ID.

I have also included the MonthYear issue in my set analysis. Highly appreciate it if you could help me with this like always  🙂

[qlikwiz123]

Hi @sunny_talwar  Any help with finding out why the original set expression is failing and counting non 'Running', 'Complete', 'Pending' status value rows?

[sunny_talwar]

You can try this

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

Not sure, but you have no values for MonthYear in your attached app