Skip to main content
Solved

How to select cases that match calculated conditions?

  • March 30, 2023
  • 6 replies
  • 143 views
T
Level 2

Hey there!

 

I'm having trouble selecting cases from a table, that match a calculated condition.

 

The data I'm trying to analyse is about quality-checks. A quality-check consists of multiple rows in which the result (minor fault/major fault/no fault/not relevant) of each checkpoint is delivered. My goal is to calculate the quality of each quality-check and display those quality-checks which didn't reach a certain percantage of quality.

 

What I already did is:

  • Calculate the quality of each check in %
    • 1- (SUM(STR_TO_INT(REMAP_VALUES("CASE_QS"."RESULT", ['no fault', '0'], ['minor fault', '5'], ['major fault', '10'], ['not relevant', '0']))) / (COUNT("CASE_QS"."CHECKPOINT")*10))
  • Create condition to evaluate whether the desired quality of 99,9% isn't reached
    • CASE WHEN (1- (SUM(STR_TO_INT(REMAP_VALUES("CASE_QS"."RESULT", ['1', '0'], ['2', '5'], ['3', '10'], ['4', '0']))) / (COUNT("CASE_QS"."CHECKPOINT")*10))) < .999 THEN 'YES' ELSE 'NO' END
  • Create OLAP-table with
    • <99.9% YES/NO
    • Count (Distinct "CASE_QS"."CHECK")
    • Date

 

As shown in the picture, the OLAP-table always shows all the checks, not only those under 99.9%. How could I achieve that?

 

Thank you very much in advance, I hope somebody can give me a hint :).

Best answer by shravya.b11

Hi @tim.schne ,

Yes, you can make use of component filter for each OLAP table.

W.r.t formula, you can may be try to use the Pull Up functions instead of normal SUM and COUNT to achieve the same.

https://docs.celonis.com/en/pull-up-aggregation.html

Hope this helps.

Thank You :)

1 Attachments

6 replies

S
Level 6
  • shravya.b11
  • Level 6
  • April 4, 2023

Hi @tim.schne ,

If i can understand you properly, you only want to view all the data where the calculation is less than 99.9%. If so, you can try to use the same formula under Load script for the OLAP table with a FILTER <condition>; Then, the data will be restricted only where the condition satisfies.

Please let me know if my assumption is not correct.

Thank you :)

T
Level 2
  • tim.schne
  • Author
  • Level 2
  • April 4, 2023

Hello @shravya.b11 ,

thank you very much for your response!

You understood me almost correctly - as I don't want to filter all results under 99,9% for all my components, but for certain ones. So I assume my best option would be to use the component-filter for each OLAP-table?

 

The filter statement I tried is:

FILTER (1- (SUM(STR_TO_INT(REMAP_VALUES("CASE_QS"."RESULT", ['1', '0'], ['2', '5'], ['3', '10'], ['4', '0']))) / (COUNT("CASE_QS"."CHECKPOINT")*10))) < 0.999

 

But this gives me the error "An aggregation is not a function. Please check that there are no dimensions and aggregations used to gether as function inputs". I also tried without brackets, but that doensn't seem to be the problem here.

S
Level 6
  • shravya.b11
  • Level 6
  • Answer
  • April 5, 2023

Hi @tim.schne ,

Yes, you can make use of component filter for each OLAP table.

W.r.t formula, you can may be try to use the Pull Up functions instead of normal SUM and COUNT to achieve the same.

https://docs.celonis.com/en/pull-up-aggregation.html

Hope this helps.

Thank You :)

T
Level 2
  • tim.schne
  • Author
  • Level 2
  • April 12, 2023

Hi @tim.schne ,

Yes, you can make use of component filter for each OLAP table.

W.r.t formula, you can may be try to use the Pull Up functions instead of normal SUM and COUNT to achieve the same.

https://docs.celonis.com/en/pull-up-aggregation.html

Hope this helps.

Thank You :)

Hello @shravya.b11 ,

thank you very much for your input, I was finally able to solve my problem.

Here is my filter condition:

 

FILTER 1 - (PU_SUM(DOMAIN_TABLE("CASE_QS"."CHECK") , STR_TO_INT(REMAP_VALUES("CASE_QS"."RESULT", ['1', '0'], ['2', '5'], ['3', '10'], ['4', '0']))) / (PU_COUNT(DOMAIN_TABLE("CASE_QS"."CHECK"), "CASE_QS"."CHECKPOINT")*10)) < 0.999

 

Thank you very much!

T
Level 2
  • tim.schne
  • Author
  • Level 2
  • April 27, 2023

I'm sorry, but I have to ask for more help.

 

As stated before, I was able to create one table that shows ALL the checks and one table that shows the checks with a quality below a certain value (aka FAILED checks). To achieve this I used the filter condition mentioned in the post above.

 

So I got those two tables:

 

date (monthly) | ALL checks

2023-02 | 4

 

date (monthly) | FAILED checks (<99.9%)

2023-02 | 2

 

 

What I would like to do now is, create ONE table that shows ALL the quality checks in one column and the FAILED quality checks in the next column. So basically like this:

 

date (monthly) | ALL checks | FAILED checks (<99.9%) | ratio

2023-02 | 4 | 2 | 50%

 

Is there any way to do this with PU-functions, as they ignore filter-statements of the component-filter?

S
Level 6

Hi @tim.schne ,

 

First of all, sorry for the late reply.

I would suggest you to use the PU function with one more argument which allows us to give filter expressions. Please find more details in the below doc.

PU functions

 

Hope this helps.

 

Thank you :)

Community Code of ConductAccessibility statement