In this community post, I am going to go over our new date filtering that allows you filter our various components for specific days without having to build a formula!
This feature is looking to make for a much more user friendly experience when filtering your data based on dates. It can be used instead of a SELECT statement where the condition is a BETWEEN function which looked something like:
Another great feature of this new functionality is that it does not require you to convert the dates into Unix time in order to filter, this is all done behind the scenes! You just need ensure that under the properties menu, your input format matches the date format in your data. The input format is also very important to ensure the dates are sorted correctly, so always double check this property!
This new feature has 7 options that will appear in the Filter Applied Action menu when the date/time format has been selected:
To access this new feature, you will need to right click either your X-Axis or the series with your dates in the component tree:
Then you will right click and select “Filter”. A window will pop up at which point you will need to click the “Condition” tab:
The first 2 options are very straightforward, they allow you to select data from today or yesterday.
The next option is “Unit to Date” which you would use for queries looking at the current week, Month to Date and Year to Date (which are selectable from a dropdown list). Essentially what this filter does is for the period length you have selected, it will find the start date of this period and return all data from then to the end of the current day. So if I were to select month, it would get set the start date to the 1st of the current month and the end date as the current date at 23:59.
“In The Last” allows you to input either a number or select a variable from a dropdown list, and then select a unit of time (minute and hour). So, if you are looking to just return data from the last 7 hours then you would type 7 into the text field and select hours from the drop which will result in the filter returning a “rolling” 7 hours (so always from the current time minus 7 hours). You can also use a variable and have connect it with a User Input Control so that you have more flexibility on being able to select a range of numbers. So if you select hours, you could have an array that contains 7, 24, 48 and 70 in the User Input Control to assign these values to the variable and give the option to filter based on 7, 24, 48 and 70 hours.
“Last Full” is very similar to the “In The Last”, the major differences being that different time periods are available in the dropdown (day, week, month, year) and that it does not return up to the minute data. The end date for this would be comparable to our TODAY() function which return the Unix equivalent time of today at 12 AM
The final 2 options go hand in hand with each other. “On or after” is exactly what it sounds like, return any data that happened on or after a certain date/time and “On or Before” is any dates that happened before a certain date/time. Essentially, On or after is a start date and on or before is an end date. The really cool feature with this is that you can either select a date from a pop up calendar that appears or you can select a variable from the dropdown if you are looking to connect 2 user input controls to the Klip and have a dynamic date range.
Another really cool feature is that you are able to add multiple conditions in this filter window. This is useful when you are looking to use 2 User Input Controls to define a start and end date (as mentioned above). You can select the dates from a User Input Control and assign those values to variables and utilize the “on or before” and “on or after” options:
One popular use case with date filter is to get the last 6 days, plus any data that has happened today. To get this data, you will need to use multiple conditions like shown above. It will look like:
What this filtering is doing, is returning the last 6 full days (yesterday minus 5), as well as midnight until now (via the today filter)
As always, if you have any questions please do not hesitate to reach out!