topic Re: Count ID based on field in QlikView

Count ID based on field

qlikwiz123 — Thu, 10 Jun 2021 14:08:05 GMT

I have two Columns - ID and Status

Status

Running

Pending

Complete

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

Re: Count ID based on field

sunny_talwar — Thu, 10 Jun 2021 15:36:05 GMT

May be this

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

Re: Count ID based on field

qlikwiz123 — Thu, 10 Jun 2021 16:12:46 GMT

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

Re: Count ID based on field

sunny_talwar — Thu, 10 Jun 2021 17:14:44 GMT

Try this with Aggr()

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

Re: Count ID based on field

qlikwiz123 — Fri, 11 Jun 2021 19:24:49 GMT

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)

Re: Count ID based on field

sunny_talwar — Fri, 11 Jun 2021 19:27:10 GMT

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?

Re: Count ID based on field

qlikwiz123 — Fri, 11 Jun 2021 20:43:03 GMT

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.

Re: Count ID based on field

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

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

Re: Count ID based on field

qlikwiz123 — Mon, 14 Jun 2021 14:04:54 GMT

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

Re: Count ID based on field

sunny_talwar — Tue, 15 Jun 2021 13:58:13 GMT

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

Re: Count ID based on field

qlikwiz123 — Tue, 15 Jun 2021 14:25:36 GMT

@sunny_talwar

Sorry. I corrected the file now.

Re: Count ID based on field

sunny_talwar — Tue, 15 Jun 2021 14:24:41 GMT

This worked for me

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

Re: Count ID based on field

qlikwiz123 — Tue, 15 Jun 2021 14:31:29 GMT

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

This is still acting up.

For ID A560591, it is counting even though it only has 1 complete and 1 Running. My condition is to count when Count of Running or Complete or Pending is greater than one.

Re: Count ID based on field

sunny_talwar — Tue, 15 Jun 2021 15:20:29 GMT

May be this

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