Ratio calculation using fixed value per row (activity name in our case)


#1

Hi all,

We want to make the observed activity counts relative to static totals per year, however we do not seem to be able to do this.

So we have 2 (relevant) tables:
ACTIVITY TABLE: with caseID, activity name, timestamp, etc.
YEAR_STATS: with activity name and COUNT_LAST_YEAR with the total number of observations of that activity last year.

Goal: divide COUNT(ACTIVITY) by COUNT_LAST_YEAR (e.g. in our analysis we have 12% of the activity occurrence of last year).
WHY: to account for relevance, 100 out of 100 is 100%, 100 out of 100.000 is 0.1%.

We currently have a table with ACTIVITY as dimension, COUNT(ACTIVITY) as KPI and the COUNT_LAST_YEAR field from the ACTIVITY_COUNT table as the 100% normalization value.

However, the result is the COUNT(ACTIVITY) value, not normalized to the (individual) field.

Are we mis-understanding this feature and how it works? (i.e. getting the value per row to normalize)

We are running Celonis 4.4.

I hope my/our question is clear :slight_smile:


#3

Hi,
Have you tried to do division inside kpi formula?


#4

Hi Nikolai,

That was actually the first thing we tried:
COUNT("PU_PM_DLG_KLA_INTEGRAAL"."PROCESOMS") / "PU_PM_PROCESCOUNT"."COUNT"

but we got the following error:

“An aggregation is not a function. Please check that there are no dimensions and aggregations used together as function inputs.”

Both of the statements, on each side of the divisor, individually work correctly.


#5

try COUNT(“PU_PM_DLG_KLA_INTEGRAAL”.“PROCESOMS”) / MIN(“PU_PM_PROCESCOUNT”.“COUNT”)

Error is due to the fact that in KPI you should always provide some grouping function.

Also check that in data model there is link between both tables. Celonis group independent tables.


#6

Hi,

That’s a neat little trick/hack! It seems to have worked. Thanks!!!