Skip to main content

Hello community,

I am trying to build a table that will have a ranking of company codes. I use Celonis 4.5 on-prem. The company codes are my dimension. This ranking is based on the below KPI logic:

CASE WHEN

MAX(WORKDAYS_BETWEEN(R2R.Tables::BSEG_KDAS.CAL_ID, R2R.Tables::BSEG_KDAS.LAST_DAY_OF_MONTH,

(PU_MAX( DOMAIN_TABLE (

R2R.Tables::BSEG_KDAS.ENTRY_DATE,R2R.Tables::BSEG_KDAS."_CASE_KEY"),R2R.Tables::BSEG_KDAS.ENTRY_DATE)))) = 3

THEN 1

WHEN

MAX(WORKDAYS_BETWEEN(R2R.Tables::BSEG_KDAS.CAL_ID, R2R.Tables::BSEG_KDAS.LAST_DAY_OF_MONTH,

(PU_MAX( DOMAIN_TABLE (

R2R.Tables::BSEG_KDAS.ENTRY_DATE,R2R.Tables::BSEG_KDAS."_CASE_KEY"),R2R.Tables::BSEG_KDAS.ENTRY_DATE)))) = 4

THEN 2

ELSE NULL

END

the above statement works as expected, however i need to include another variable where it also checks for the time stamp.

I have tried the below statement, however am receiving an error message saying An aggregation is not a function.

Can you please suggest a solution that incorporates both the max day function and the time stamp?

CASE WHEN

(R2R.Tables::BSEG_KDAS.CPUTM > 120000 AND

MAX(WORKDAYS_BETWEEN(R2R.Tables::BSEG_KDAS.CAL_ID, R2R.Tables::BSEG_KDAS.LAST_DAY_OF_MONTH,

(PU_MAX( DOMAIN_TABLE (

R2R.Tables::BSEG_KDAS.ENTRY_DATE,R2R.Tables::BSEG_KDAS."_CASE_KEY"),R2R.Tables::BSEG_KDAS.ENTRY_DATE)))) = 3)

THEN 1

WHEN (R2R.Tables::BSEG_KDAS.CPUTM < 120000 AND

MAX(WORKDAYS_BETWEEN(R2R.Tables::BSEG_KDAS.CAL_ID, R2R.Tables::BSEG_KDAS.LAST_DAY_OF_MONTH,

(PU_MAX( DOMAIN_TABLE (

R2R.Tables::BSEG_KDAS.ENTRY_DATE,R2R.Tables::BSEG_KDAS."_CASE_KEY"),R2R.Tables::BSEG_KDAS.ENTRY_DATE)))) = 3)

THEN 2

ELSE NULL

END

Thanks,

Anna

Hi Anna,

For me its hard to fully dissect your code quickly. My gut feeling says that your MAX function right after the AND wont work as expected.

In general its a good idea to start small and build it up. So could you create a (temporary) KPI where you try your timestamp logic from small (like a first step you need), then building it up to your intended end result. This usually helps making the steps small and the exact problem clear.

Good luck!

Hi Anna,

as @joosbuijs already said: The issue is near the MAX call in your CASE WHEN condition. The first query works just fine because the CASE WHEN is simply built upon the MAX. However, in the second query, you add a dimensional column without an aggregation around it, and you cannot do that. So you cannot mix aggregates and non-aggregates inside the same statement. In SQL, this would also fail.

You can fix it by also adding an aggregation function (MIN, MAX, SUM, ) around the R2R.Tables::BSEG_KDAS.CPUTM column you have added to your condition, such that everything is again purely based on aggregations.

Another possibility would be to move the condition youve added inside the MAX calls, if possible.

Best

David

Reply


Terms & Conditions

ANY QUESTION?

Talk to an expert
FAQs
Contact us

MORE CELONIS

Academy arrow_outward
Documentation arrow_outward
Support Center arrow_outward
Partner Portal arrow_outward
Marketplace arrow_outward

CHECK US IN

LinkedIn
YouTube
Instagram
Facebook
Imprint
Disclaimer
Privacy Notice
Cookie Notice
Terms and Conditions
Celonis © 2024