Problems when filtering data with the datetime operator later than/ earlier than

How To
,
1

For my scenario, I want to use the datetime operator to filter whether a date (yesterday) is in a certain period (start day and end day). Because I intend to filter independently of the time of day, I use a variable in which I set the hour, minute and second to null. For comparison, I have also created a variable for today. The filter (earlier than and later than) does not work if the start day or the end day are the same as the variable “yesterday”. I took a closer look at the ouput and discovered that “yesterday” does not result in -1 today, although it is correct in the display of the variable. How can I make the data more precise?
Variables yesterday and today:

grafik
grafik638×766 26 KB

grafik
grafik
Compared to start day (Abwesenheit_ab) and end day (Abwesenheit_bis):
grafik
grafik609×519 20.5 KB

grafik

Thanks for your help!

2

Hi @Lena_Sue

Welcome to community :slightly_smiling_face:

Use filter using {{formatDate()}} function.
Input:

image
image828×403 32.7 KB

Output:
image
image899×539 43.8 KB

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support
Visit us here
Youtube Channel

1 Like
3

Hi, Thanks for your quick answer!
My understanding is that formatDate converts the date into a text. Since I have to work with the datetime opertor earlier than/later than, does this only work with the date format?

4

I tried first formatting it into DD.MM.YYYY and then parse it into date format.

grafik
grafik637×746 28.2 KB

But the date shown in the history of the module is not the same as in the output budle:
grafik
grafik
I thought this could be a problem with the timezones. But even after I put that information into the parse function, nothing changed.

5

I have now solved it by first converting the date into the format DD.MM.YYYY with formatDate and then adding an hour to compensate for the different time zones of the systems (addHours). The addHours function turns this back into a date format that works for the Datetime filter.

grafik
grafik615×589 19.3 KB

Thanks for your input!

2 Likes
6

Hi @Lena_Sue welcome to the community :wave:

I just wanted to quickly say congrats on getting this up and running with the assistance of @Msquare_Automation :clap:

Also, thank you very much for sharing what did the final trick for the benefit of the community. We all truly appreciate it. :pray:

1 Like