Hi @rita.kiss,
What is the content of the two variables you are using in this query? Does the OLAP table have other dimensions that could cause the error? And which Celonis product are you using (CPM4.5, CPM4.6, IBC, )?
Best
David

---

Hi David,
There is no OLAP table, its just the variables.

1. Variable 1: TPT_Unit with possible values: days, hours, minutes
2. Variable 2: TargetCT_Days - this is input via variable input, number
3. TargetCT_Calculated: this is supposed to be calculated (see case when statement in the picture). The calculation works without the case when statement so its not a data format issue. Also, I tried to wrap it in an aggregation function - that also doesnt help.
   We have on-premise v1.23 - I dont know if that answers the question. If not, please let me know where I can see the CPM number.
   Thank you!

---

Hi,
unfortunately I cannot reproduce your issue. Please check the following things:

• You need to wrap single quotes around the time unit value that comes from your variable. You either can put them around the unit string inside the variable (TPT_Unit content would be 'Days', not Days ), or you put them where you call the variable in your CASE WHEN ('<%=TPT_Unit%>' instead of <%=TPT_Unit%>).
• You cannot nest variables. If you are trying to create a variable TargetCT_Calculated using the other two variables TPT_Unit and TargetCT_Days, that will not work. However, you can use variables inside saved formulas.

If that does not solve your issue, please create a ticket at our servicedesk via https://servicedesk.celonis.com or e-mail to servicedesk@celonis.com . In the ticket, please also mention the version number and the content of your variables.
Thanks and best regards
David

---

Hello,
I have a similar issue. Was this issue solved?
There is a dropdown to select the unit which will set a variable called unit with values:
days
hours
minutes

Now I have a formula:

CASE
WHEN <%=unit%> = minutes THEN Trimmed_Mean(CALC_THROUGHPUT( FIRST_OCCURRENCE [ <%= from %> ] TO FIRST_OCCURRENCE [ <%= to %> ], REMAP_TIMESTAMPS ( _CEL_O2C_ACTIVITIES.EVENTTIME, minutes)))
WHEN <%=unit%> = hours THEN Trimmed_Mean(CALC_THROUGHPUT( FIRST_OCCURRENCE [ <%= from %> ] TO FIRST_OCCURRENCE [ <%= to %> ], REMAP_TIMESTAMPS ( _CEL_O2C_ACTIVITIES.EVENTTIME, minutes))) / 60
WHEN <%=unit%> = days THEN Trimmed_Mean(CALC_THROUGHPUT( FIRST_OCCURRENCE [ <%= from %> ] TO FIRST_OCCURRENCE [ <%= to %> ], REMAP_TIMESTAMPS ( _CEL_O2C_ACTIVITIES.EVENTTIME, minutes))) / 1440
END

But it does not work. Can you give me a hint?
Celonis error message is unfortunately not very helpful:

I am using 4.6 on-premise.
Thank you.
Best regards,
Marcel

---

Hi Marcel,
I have never managed to solve my original issues but I think you can achieve in a simpler way what you are trying to do.
Just use the <%=unit%> variable within the CALC_THROUGHPUT / REMAP_TIMESTAMPS function and you dont need the CASE WHEN statement.
I mean, like:
TRIMMED_MEAN(
CALC_THROUGHPUT(
FIRST_OCCURRENCE[<%= from %>] TO LAST_OCCURRENCE[ <%= to %>],
REMAP_TIMESTAMPS(_CEL_O2C_ACTIVITIES.EVENTTIME, <%= TPT_Unit %>)))
This will automatically calculate the throughput time in the selected unit so you dont need the case when statement or the division.
Btw the error message looks like you miss a closing ) somewhere in the code.

---

Hi Rita,
thanks for your reply
To be honest, we had the formula you mentioned until now. However we are not satisfied with it because:
If from-activity happens at 01.01.2020 11pm and to-activity happens at 02.01.2020 01am. The throughput in hours is 2.
However if we switch now to unit days it returns 1 --> But 2 hours is not even close to 1 day.
Do you know if there is a possibility to add decimals to the value so it would return something like 0,x days?
If not, that is why we want to do the calulation from minutes to hours and days on our own to have the decimals.
Thank you for your help.

---

You can change the format so it displays 1 decimal (or 2 if preferred) - at least thats what I did to solve the problem you mentioned.

---

Some more remarks: your error message is due to using single quotes around minutes in the REMAP_TIMESTAMPS function. It should be like this:
TRIMMED_MEAN (
CALC_THROUGHPUT( FIRST_OCCURRENCE [ <%= from %> ] TO FIRST_OCCURRENCE [ <%= to %> ],
REMAP_TIMESTAMPS ( _CEL_O2C_ACTIVITIES.EVENTTIME, minutes)))
Unfortunately, that doesnt solve the problem as it will still give you the error message from my original post (Table compiler not implemented.)

---

Hello Rita,
thank you for your help. I tried, but the result is 1.00 days
I assume that the calc_throughput function calculates 02.01.2020 - 01.01.2020 = 1 Day and thats it.
Can anyone of Celonis confirm this or let me know how it works, please?
Thank you!!

---

Hi @Mankem
If you use DAYS in REMAP_TIMESTAMPS, the time of the day is completely ignored. This is because REMAP_TIMESTAMPS returns the number of specified time units since the 1970-01-01.
So REMAP_TIMESTAMPS with DAYS will return integer 0 for 1970-01-01, 1 for 1970-01-02, and so on, independent of the time of day. CALC_THROUGHOUT then in essence just calculates the difference between these integer numbers corresponding to the FROM and TO activity.
In order to take also the time into account, you can use HOURS, MINUTES or SECONDS instead and divide the CALC_THROUGHPUT result to get days again.
So if you use HOURS (then you will have hour-precision in your result), divide the CALC_THROUGHPUT result by 24. If you use MINUTES (minute-precision), divide the CALC_THROUGHPUT result by (24*60).
Best
David

---