This is a PQL question: - I have three THROUGHPUT calculations working (I think) - I now want to have a COUNT column next to each. But:I am counting ACTIVITIES not CASES.How can I count cases, not activities/tasks?
Best answer by Sverre Klein
Thank you! I will try three separate tables, one for each Throughput.
- However, what I am trying to do might be a little different. Here is an image ... I'd like to have a COUNT column to the right of each of these THROUGHPUT columns.,
- I will experiment with what you've given me.
- Can I tweak COUNT_TABLE so it is filtered to each THROUGHPUT type?
Also ... thank you for teaching me while also giving me the information. That is the most helpful over the long run.
No problem, happy I can be of help :)
In terms of keeping it simple and to get to the result you want, I would recommend making 3 separate OLAP tables with different filters.
Here's how I would do it:
OLAP table 1 that focuses on cases with status CLOSED
OLAP table 2 that focuses on cases with status CLOSE
OLAP table 3 that focuses on cases with neither Status CLOSED or CLOSE.
All the tables will have the same layout, with different Component filters
- So table 1 will look like this.
RESOURCE is a dimension (Manufacturer in your example)
Throughput with status closed is an KPI
Case count with status closed is an KPI.
- We will put a component filter on the table, this can be done by right-clicking on the OLAP table and select "Component filter".
- Enter the following code. FILTER "CASE_TABLE_PO"."CUMULATIVE_NET_WORTH" = 2000;. I'm not sure which table and column the CLOSED and CLOSE statuses can be found, but I assume it would be something like this. FILTER "TRIMMEDCombined_Activity_Access_CASES"."STATUS_1" = 'CLOSED';
- The Entire table is now filtered on cases with the status Closed.
Here is a before and after of my table.
Before the filter of Cumulative_Net_Worth = 2000
After applying the filter
Then repeat it for the other two tables with the appropiate component filters!
Let me know if you got it working!
+14Hi Karl,
First, this answer assumes you have a case table.
I have quickly created an OLAP table to see if you are looking for this outcome:
RESOURCE is a dimension
Throughput calculation is an KPI
Case count is an KPI
The case count is available whenever you have a case table. The code would be COUNT_TABLE("YOUR_CASE_TABLE")
Hopefully this is what you are looking for, let me know if you need more help. :)
Hi Karl,
First, this answer assumes you have a case table.
I have quickly created an OLAP table to see if you are looking for this outcome:
RESOURCE is a dimension
Throughput calculation is an KPI
Case count is an KPI
The case count is available whenever you have a case table. The code would be COUNT_TABLE("YOUR_CASE_TABLE")
Hopefully this is what you are looking for, let me know if you need more help. :)
Thank you! I will try three separate tables, one for each Throughput.
- However, what I am trying to do might be a little different. Here is an image ... I'd like to have a COUNT column to the right of each of these THROUGHPUT columns.,
- I will experiment with what you've given me.
- Can I tweak COUNT_TABLE so it is filtered to each THROUGHPUT type?
Also ... thank you for teaching me while also giving me the information. That is the most helpful over the long run.
Your COUNT_TABLE solves the Activities double-count problem. (see below)
But ... as I try to filter the COUNT-TABLE I am running into syntax errors.
My research also hinted that this approach would work (at filtering a COUNT_TABLE by an activity), but the syntax is failing:
SELECT COUNT(*) FROM COUNT_TABLE FILTER "TRIMMEDCombined_Activity_Access"."STATUS_1" = 'CLOSED')
+14Thank you! I will try three separate tables, one for each Throughput.
- However, what I am trying to do might be a little different. Here is an image ... I'd like to have a COUNT column to the right of each of these THROUGHPUT columns.,
- I will experiment with what you've given me.
- Can I tweak COUNT_TABLE so it is filtered to each THROUGHPUT type?
Also ... thank you for teaching me while also giving me the information. That is the most helpful over the long run.
No problem, happy I can be of help :)
In terms of keeping it simple and to get to the result you want, I would recommend making 3 separate OLAP tables with different filters.
Here's how I would do it:
OLAP table 1 that focuses on cases with status CLOSED
OLAP table 2 that focuses on cases with status CLOSE
OLAP table 3 that focuses on cases with neither Status CLOSED or CLOSE.
All the tables will have the same layout, with different Component filters
- So table 1 will look like this.
RESOURCE is a dimension (Manufacturer in your example)
Throughput with status closed is an KPI
Case count with status closed is an KPI.
- We will put a component filter on the table, this can be done by right-clicking on the OLAP table and select "Component filter".
- Enter the following code. FILTER "CASE_TABLE_PO"."CUMULATIVE_NET_WORTH" = 2000;. I'm not sure which table and column the CLOSED and CLOSE statuses can be found, but I assume it would be something like this. FILTER "TRIMMEDCombined_Activity_Access_CASES"."STATUS_1" = 'CLOSED';
- The Entire table is now filtered on cases with the status Closed.
Here is a before and after of my table.
Before the filter of Cumulative_Net_Worth = 2000
After applying the filter
Then repeat it for the other two tables with the appropiate component filters!
Let me know if you got it working!
Thank you ... I will try this.
- I guess you're telling me that the COUNT_TABLE feature can't be filtered inside the Expression/KPI.
Good to know.
+14Thank you ... I will try this.
- I guess you're telling me that the COUNT_TABLE feature can't be filtered inside the Expression/KPI.
Good to know.
You can use filters with aggregations like Count, average as such with Pull-up (PU) functions. Pull-Up-functions are used to achieve nested aggregations and filters on aggregations in Celonis.
However these are a bit more complex and what I showcased earlier is simple yet effective alternative. Be free to experiment with it though! 😎
Thank you Sverre ... I am experimenting. You're terrific.
- I am running into one inconsistency, though:
- My Activity table has the regular table, and CELONIS automatically created a second 'CASE' table (version of the Activity table).
- "TRIMMEDCombined_Activity_Access"."STATUS_1" = 'CLOSE' (this contains the activities/tasks)
- "TRIMMEDCombined_Activity_Access_CASES" only contains the CLAIMID field.
- My 'real; CASE table is:
- "TRIMMEDCombined_Case"
I just want to be sure that I am not causing a problem by using "TRIMMEDCombined_Activity_Access_CASES" in the configuration script.
- That is the table containing the Activities/Tasks.
FYI ... it is working perfectly.
- I double checked the math and filtering ... all matched up!
Thank you
+14It is indeed normal that Celonis automatically creates a case table for you with the Case IDs as the only field (CLAIMID in your example) if you did not do so.
In this case you must use the activity table STATUS_1 field with the 'CLOSE' filter, otherwise you won't filter on it properly, since the activity table contains the STATUS_1 data you want to filter on.
Let me know if you need more assistance on this matter 😄
+14FYI ... it is working perfectly.
- I double checked the math and filtering ... all matched up!
Thank you
Happy I could help out Karl, don't be afraid to ask for help if you need it in the future!
All is good. I have the two tables. Now I am figuring out how to create the 'or'.
- It appears that I have to use SELECT to create the neither CLOSE nor CLOSED filter?
So ... I feel like I am abusing your help ... but I've gone through my lessons and I've tried a few approaches and filtering out CLOSE and CLOSED isn't working right ... here are a few attempts:
- FILTER "TRIMMEDCombined_Activity_Access"."STATUS_1" NOT LIKE 'CLOSE'
- This had hope because both words start with CLOSE.
- but this brought back the full 100% count of the cases
- FILTER "TRIMMEDCombined_Activity_Access"."STATUS_1" = 'CLOSED' OR "CLOSED"
- but this has syntax errors
- FILTER "TRIMMEDCombined_Activity_Access"."STATUS_1" = 'CLOSED';
FILTER "TRIMMEDCombined_Activity_Access"."STATUS_1" = ''CLOSE'
- But this brought back zero
I even tried to create a formula (subtracting the results from CLOSE and CLOSE) but since this is a Component Filter, I realized a formula would never work.
What am I doing wrong?
No question this time ... I now have all three filters working.
- I don't know what was wrong earlier, but the NOT LIKE approach is working.
THANK YOU FOR ALL OF YOUR PATIENCE WITH ME!
+14No question this time ... I now have all three filters working.
- I don't know what was wrong earlier, but the NOT LIKE approach is working.
THANK YOU FOR ALL OF YOUR PATIENCE WITH ME!
Great Karl, didn't see your question earlier, sorry about that. 😓
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.