Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this:
=sum(
aggr(count(distinct if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>}ID),ID,Status)>1,1)),ID)
)
HI
Try like below
Count(DISTINCT {<Status = {'Running', 'Complete', 'Pending'}>} ID)
Count(DISTINCT {<Status = {'Complete'},Date ={">=$(=MonthStart(addmonths(today(),-12)))"}>}ID)
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))
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
Can you please remove the Status from the dimension
Removed. Still see the count for ID A560591
Please , try this :
sum(if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>}ID),ID,Status)>1,1,0))
Still does not work.
The ID is being counted twice here. It should be counted only once if it satisfies the conditon.
count(distinct if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>}ID),ID,Status)>1,1))
Just replace the sum with Count
Hi,
This seems to be working in a table with ID as dimension.
How do I show this number in a text box?