How to calculate number of cases that repeat an activity

Hi!

I’m trying to calculate how many cases have a certain activity less or more than X times, etc.

I have this table A which shows number of cases that have a certain activity, and total sum of this activity. As you can see, the activity is repeated many times in a few cases.

I want to separate in ranges, I’m using this formula but I get the total number of activities and not the number of cases. (Table B)

SUM(CASE WHEN
PU_COUNT("_CEL_PS_PO_LINE", “CEL_P2P_ACTIVITIES"."ACTIVITY<%=language%>”)<=10 THEN 1.0
ELSE 0.0 END)

Could you please help me know what I’m doing wrong?

Thank you!

Table A
image

Table B

1 Like

Hi,

I imagine using the stated formula gives the amount of activities because you are using the activities as a dimension + returning 1 as the result of the case when. This leads to the fact that the 1 is counted on the activity level.

You can try using following formula which explicitly states that cases should be counted:

COUNT(DISTINCT CASE WHEN
PU_COUNT("_CEL_PS_PO_LINE", “ *CEL_P2P_ACTIVITIES"."ACTIVITY* <%=language%>”)<=10 THEN "_CEL_PS_PO_LINE".CASES
ELSE NULL END)

Best regards,
Viana

Viana, thank you so much for replying. I will try this option and let you know the result. :slight_smile: