How to calculate number of cases that repeat an activity

Question

Hi!Im 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, Im using this formula but I get the total number of activities and not the number of cases. (Table B)SUM(CASE WHENPU_COUNT("_CEL_PS_PO_LINE", CEL_P2P_ACTIVITIES"."ACTIVITY<%=language%>)<=10 THEN 1.0ELSE 0.0 END)Could you please help me know what Im doing wrong?Thank you!Table ATable Bimage1044173 6.21 KB

viana.kalve11 · Answer

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 WHENPU_COUNT("_CEL_PS_PO_LINE",  *CEL_P2P_ACTIVITIES"."ACTIVITY* <%=language%>)<=10 THEN "_CEL_PS_PO_LINE".CASESELSE NULL END)Best regards,Viana

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN