For instance, ... 5 WO's have 3 Distinct Claim ID's, 7 WO's have 2 Distinct Claim ID's, and 125 WO's have 1 Claim ID.
Thank you ... I am sure this is simple, but I can't find how to do it.
Solved
I am trying to create a column chart which the count of the number of Claim ID's in a Work Order.
Best answer by Sverre Klein
Oh ... this results in a KPI.
What I need is a table (that I can click into and would filter on) .... which looks like this:
Cases With
x# Claim#s Count
1 450000
2 5000
3 150
4 75
5 20
6 2
9 1
I hope this looks like a two-column table
@karl.guent, Ah okay, apologies, this makes sense!
The Claims column would then indeed be your dimension. And the case count your KPI (if I understand correctly now 😆 ). Since you are getting the claims and workorder data from the same table, you should use the DOMAIN_TABLE function.
Your code would look something like this.
Dimension:
PU_COUNT_DISTINCT( DOMAIN_TABLE( "YourTable"."YourWorkIDColumn"), "YourTable"."YourClaimIDColumn")
KPI:
Count the amount of cases.
Im not whether your WorkID or Work Order is your case identifier. So play around with this, and let me know if you get the result you wanted 😁
+14Hi @karl.guent,
Good to see you again on the forums!
I'm pretty sure in your situation you can use COUNT ( DISTINCT "table"."column" ) as an KPI in your column chart.
If in your situation you have multiple columns. You can use
UNIQUE_ID which is inside a COUNT DISTINCT to count the number of unique combinations of values.
COUNT ( DISTINCT UNIQUE_ID ( "Vendors"."City" , "Vendors"."Country" ) )
Let me know if this is worked for you.
Kind regards,
Sverre Klein
Hmmmm .... I have been working with that.
- When I use Work Order in the Dimension, I get a count of "the number of Claim IDs for each Work Order number (which means WO# 1 had 2 Claim ID's ... WO#2 had 4 Claim ID's ... and so on. Since I have over 500k Work Orders, I get 500k rows in the response)
- What I am looking for is "The number of Work Orders with 2 ClaimIDs, the number of WOs with 3 ClaimIDs, the number with 4 ClaimIDs, etc.
- There are very few with more than 5 ClaimIDs (and it's almost always a problem) so this count will be really helpful in filtering my populations.
So ... in a sense, what I am looking for is a "Bundling" of the Count which results from bullet #1 above.
Does that help to clarify what I am trying to do?
+14Hi @karl.guent,
Sounds like you need a PU function here in what you are trying to achieve. Not sure if I fully understand the question.
Could you specify whether:
- You want to count how many Work Orders (cases) have 2 or more Claim IDs, or
- You want to count how many claim IDs there are in total.
Awaiting your response.
Kind regards,
Sverre Klein
I can work with ""count how many Work Orders (cases) have 2 or more Claim IDs"
+14Then I would do the following @karl.guent,
In my example it looks like this
SUM(CASE WHEN PU_COUNT_DISTINCT ( "CASE_TABLE_HUBSPOT" "ACTIVITIES_TABLE_HUBSPOT"."ACTIVITY_NAME" ) > 1 THEN 1 ELSE 0 END)
The end result from this KPI is the amount of cases that have 2 or more distinct activity name values.
So in your situation, I'd assume it would look something like this.
SUM(CASE WHEN PU_COUNT_DISTINCT ( "WORK_ORDER_TABLE" "CLAIM_ID_TABLE"."CLAIM_ID COLUMN" ) > 1 THEN 1 ELSE 0 END)
Let me know if this worked or if there are any more questions of course 😊
Kind regards,
Sverre Klein
Oh ... this results in a KPI.
What I need is a table (that I can click into and would filter on) .... which looks like this:
Cases With
x# Claim#s Count
1 450000
2 5000
3 150
4 75
5 20
6 2
9 1
I hope this looks like a two-column table
ugh ... spaces got cut out ... imagine "Cases With x#" is a column header and "Claims Count" is the second column .... then 1, 2, 3, 4, 5, 6, 9 are the left column of data and the other numbers are the right column of data.
Hmmm ... I think I see what is confusing.
- ClaimID and WorkOrder number are two fields in the same table, not two fields in a Case / Activity parent->child relationship (no 1:N) relationship.
- Sometimes, two ClaimIDs come from the same Work Order because our system split one Work Order into two different ClaimIDs because parts in the repair came from different Vendors (ex: a light bulb General Electric, and a filter from Mercedes Benz ).
- So ... two Claims are created even though they are part of the same repair (Work Order)
- Out of my 250k Work Orders, I am trying create a table which displays the number of WO's with 1 ClaimID, another row with the number of WO's with 2 ClaimIDs, another row with WO's with 3 ClaimIDs .... etc.
The PU doesn't work because there are not "Two Separate Tables" with a 1:N relationship, instead, all of the data for this OLAP Table (or Column Chart) is from one source-table.
- In case this helps .... ClaimID is the CELONIS "Case" Column
- For the 250k Claim IDs in this Dashboard, there are 120k Work Order Numbers
- Some of the Claim ID rows are from the same Work Order.
+14Oh ... this results in a KPI.
What I need is a table (that I can click into and would filter on) .... which looks like this:
Cases With
x# Claim#s Count
1 450000
2 5000
3 150
4 75
5 20
6 2
9 1
I hope this looks like a two-column table
@karl.guent, Ah okay, apologies, this makes sense!
The Claims column would then indeed be your dimension. And the case count your KPI (if I understand correctly now 😆 ). Since you are getting the claims and workorder data from the same table, you should use the DOMAIN_TABLE function.
Your code would look something like this.
Dimension:
PU_COUNT_DISTINCT( DOMAIN_TABLE( "YourTable"."YourWorkIDColumn"), "YourTable"."YourClaimIDColumn")
KPI:
Count the amount of cases.
Im not whether your WorkID or Work Order is your case identifier. So play around with this, and let me know if you get the result you wanted 😁
Sverre ... THANK YOU!
Your response about DOMAIN_TABLE was perfect - and elegant.
Thank you sooo much. Now that I understand this concept, it is going to be really useful in several cases.
You Are The Best!!
+14Sverre ... THANK YOU!
Your response about DOMAIN_TABLE was perfect - and elegant.
Thank you sooo much. Now that I understand this concept, it is going to be really useful in several cases.
You Are The Best!!
Glad to hear I could be of help @karl.guent, don't hesitate to ask more questions in the future!
If I want to calculate the amount of cases for each class out of the total cases, how can i do this?
For example 7 WO's have 2 Distinct Claim ID's out of 100 WO's to share the rate per each class.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.