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