Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
May be this
Count(DISTINCT {<ID = {"=Count({<Status = {'Running', 'Complete', 'Pending'}>} Status) > 1"}>} ID)
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
Try this with Aggr()
Aggr(If(Count(DISTINCT {<ID = {"=Count({<Status = {'Running', 'Complete', 'Pending'}>} Status) > 1"}>} ID)>=1, ID, Null()), ID)
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)
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?
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.
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 🙂
Hi @sunny_talwar Any help with finding out why the original set expression is failing and counting non 'Running', 'Complete', 'Pending' status value rows?
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