Trying to count a condition based on a specific column

[Fahad]

Hey all,

Am facing an Issue calculating a specific requirement I have two columns one for the status and the other Is the ID

the Issue Is some of the values are duplicated based on the ID for example:

ID	status
1	canceled
1	canceled
2	canceled
2	canceled
3	active
3	canceled
4	active
4	canceled
4	active
4	canceled

So I wanted to count the times the canceled status has came and for the times that the ID status only came Canceled need it to count as one and for the others count as Is, As above shown we have four IDs two has only canceled status they need to be counted as 2 canceled status rather then 4 and the others should be counted as Is and the final result should be 5 records has canceled status hope I explained It well enough.

what I reached till now is :

if(Count(distinct status ='CANCELED') =1 , status ,1)

am rather new at this so I appreciate your Insight,

at the end we need a KPI that displays the number of canceled records based on the above requirement i would appreciate your support In this .

[GaryGiles · Accepted solution]

You can use the P() and E() element functions in set analysis to help with this calculation.  You will also want to break it into 2 expressions and add them together, like this:

count({$<status={'canceled'},ID=E({$<status-={'canceled'}>} ID)>} Distinct ID) +

count({$<status={'canceled'},ID=P({$<status-={'canceled'}>} ID)>} ID)

The first count gives you the distinct IDs that have a canceled status, but Excludes those IDs with other status records.  The second count gives you a count of ID (not distinct IDs) that have a canceled row and also records with other statuses.

Hope that helps.

[Fahad]

Hi Gary

Thanks For your support.