How to create a benchmark that compares two time frames?

Hello,

Use 2x Button dropdowns (1x for start date and 1x for end date) and assign selected dates to variables. Then filter process explorer using that variables. Button dropdown is maybe not fancy as the Date Picker however they are felxible and can apply only to specific elements, making benchmarking possible.

Please not that it won't cause normal selection visible on top of the sheet.

Best Regards,

Mateusz Dudek

Thanks Mateusz.

Yes, that's a great idea, but it gives me the following error:

1. I have set the variables

2. I have filtered the process explorer, but it reports an error on the first variable:

Do you have any idea why?

Also, as you said, the button dropdown is not great because it shows me the individual timestamps (including minutes), so making a filter in two days is more complicated.

Hello,

1) I've tried to use button dropdown, however it seems it's not working correctly. The only option that currently works is using variable input, write there values like '2022-04-04' and then use filtering like this:

"ActivityTable"."EVENTTIME" BETWEEN TO_DATE(<%= Process1_StartDate %>, FORMAT('%Y-%m-%d')) AND TO_DATE(<%= Process2_EndDate %>, FORMAT('%Y-%m-%d')),

It's probably some kind of error, because even if preview says that output is YYYY-MM-DD it will produce strange string like this:

"Sat Jan 04 2020 01:00:00 GMT+0100 (Central European Time)". String value cannot be used with "BETWEEN" function, and I couldn't convert Sat Jan 04 2022' to normal YYYY-MM-DD date.

2) You can use function ROUND_DAY + set output formating to %Y-%m-%d to avoid seeing 00:00:00 time.

Best Regards,

Mateusz Dudek

First of all, Mateusz thank you for your invaluable help.

1) unfortunately it doesn't work for me anyway: it always returns the same error on the first variable. The reason may be, as you say, that it creates a date in a textual format that is not accepted by the BETWEEN function. Unfortunately, even with the FORMAT function it doesn't work for me.

2) Thanks, I succeeded and it works

I will wait for a solution from Celonis or other proposal

Regards

Enrico

Can you show which error is occurring?

The other solution is to store date as text (then is best to have it in format YYYY-MM-DD because of alphabetical sorting), it needs to be done in SQL, as PQL doesn't allow to convert date to text (only text to date is working). Then it can be stored in static value variable without any problems, and be used in filtering. The formula above that uses TO_DATE should work fine then.

Best Regards,

Mateusz Dudek

Obviously the mentioned tables exist and the variant exists.

The option to set the date as text does not seem very convenient to me because I use that field to create many other components that are based on the DATE type.

Hi Enrico/Mateusz,

Unfortunately like you say you are not able to use two independent Date pickers.

Using a text variable and then transform it to date could be done with formula TO_DATE (https://help.celonis.cloud/help/display/CIBC/TO_DATE).

In fact, another solution maybe more "user friendly" for the analysis although a bit more complex on the back end would be to include 3 button dropdowns for each date. On the 1st you set the day, on the second you set the month, on the 3rd you set the year. Something like this:

You save each value to a given variable and then you can use that date building it as:

TO_DATE(<%= DAY_INICIO %>||'-'||<%= MONTH_INICIO %>||'-'||<%= YEAR_INICIO %>,FORMAT('%d-%m-%Y'))

TO_DATE(<%= DAY_FIN %>||'-'||<%= MONTH_FIN %>||'-'||<%= YEAR_FIN %>,FORMAT('%d-%m-%Y'))

Hope these points help you solve your issue.

Best regards,

Pablo