Remapping work days

Question

Hi,I have a problem that was addressed in another topic however, I have tried the solution and it did not work.Basically i would like to sum up the euro value of invoices that have been completed within 2 working days. We dont have the calendar table in our data model so using the working day formula does not work and therefor I have tried the below work around that has been suggested. However, am getting an error.Can you please let me know where my error is?SUM( CASE WHEN DAYS_BETWEEN( REMAP_TIMESTAMP (ROUND_DAY(BKPF.BLDAT, MONDAY 08:00-17:00 TUESDAY 08:00-17:00 WEDNESDAY 08:00-17:00 THURSDAY 08:00-17:00 FRIDAY 08:00-17:00 ),REMAP_TIMESTAMP (ROUND_DAY(BSEG.AUGCP, MONDAY 08:00-17:00 TUESDAY 08:00-17:00 WEDNESDAY 08:00-17:00 THURSDAY 08:00-17:00 FRIDAY 08:00-17:00)) <=2.0 THEN BSEG.EUROELSE NULLEND)Thanks for your help!Anna

david.beche12 · Answer

Hello Anna,REMAP_TIMESTAMPS returns an integer, so you cannot use it inside DAYS_BETWEEN (which only works on timestamps).REMAP_TIMESTAMPS returns the number of time units you specify (DAYS, MINUTES, ) since 1.1.1970 as an int. So by subtracting two REMAP_TIMESTAMPS results, you get the difference between the corresponding timestamps in the specified time unit.So your query would look like this:SUM( CASE WHENREMAP_TIMESTAMPS ( BSEG.AUGCP, DAYS, MONDAY 08:00-17:00 TUESDAY 08:00-17:00 WEDNESDAY 08:00-17:00 THURSDAY 08:00-17:00 FRIDAY 08:00-17:00)-REMAP_TIMESTAMPS ( BKPF.BLDAT, DAYS, MONDAY 08:00-17:00 TUESDAY 08:00-17:00 WEDNESDAY 08:00-17:00 THURSDAY 08:00-17:00 FRIDAY 08:00-17:00) < 2 THEN BSEG.EUROELSE NULLEND)You can find an example how to use REMAP_TIMESTAMPS to calculate a time difference in the documentation of REMAP_TIMESTAMPS.BestDavid

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN