Hi all,
I want to calculate the cycle time with the “workdays_between” function. Is it somehow possible to calculate on a minute level?
WORKDAYS_BETWEEN(“VBAP”.“FABKL”, PU_FIRST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“EVENTTIME”, “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” IN (<%=Non_ESOP_Activities%>)),
PU_LAST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“EVENTTIME”, “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” IN (<%=Non_ESOP_Activities%>)))
Thanks a lot in advance.
Best regards,
Jonas
Hi Jonas,
you can use REMAP_TIMESTAMPS for this. By subtracting two REMAP_TIMESTAMPS values, you can encode the WORKDAYS_BETWEEN behavior, but with the flexibility that you can define the time unit.
It would look like this:
REMAP_TIMESTAMPS( PU_LAST(...) , MINUTES,
WORKDAY_CALENDAR ( TFACS_table ), "VBAP"."FABKL" )
-
REMAP_TIMESTAMPS( PU_FIRST(...) , MINUTES,
WORKDAY_CALENDAR ( TFACS_table ), "VBAP"."FABKL" )
Please note that in contrast to WORKDAYS_BETWEEN, it does not take the TFACS table which you configured in the Calendar Settings of the Data Model. Instead, you need to add the TFACS table to the Data Model like any other table and reference it inside the WORKDAY_CALENDAR function. You don’t have to join the TFACS table to another table.
Hope this works for you!
Best
David
Hi David,
many thanks for your reply.
I will test it and then I will give you feedback if it worked.
Best regards,
Jonas
Hi David,
I have tested it but I get some errors. As you mentioned I have added the TFACS Table to the O2C data model.
With the formula below I get still an error (not a valid PQL expression). Do you have an idea what the reason is for that error?
Many thanks for your support.
Best regards,
Jonas
Hi Jonas,
it looks like you are on an on-prem CPM4 installation, is that correct? The mentioned WORKDAY_CALENDAR feature is rather new and currently only available in the EMS. It will also be shipped with the upcoming CPM4.7 release. In versions CPM4.6 and earlier, unfortunately, this feature is not available and therefore there is no solution available here.
Best
David
Hi David,
yes exactly, we are on an on-prem CPM4 installation.
Ok that’s a pity, but thanks a lot for your support.
Have a nice week.
Best regards,
Jonas
