Filter for date range

George Brown
George Brown Member
edited December 2023 in General

I would like to filter in a workflow for dates within the last 7 days from today. Right now the only way I can see how is to have two different filters.

The first filter step takes the current date and time, subtracts 7 days, and then uses a "Greater Than or Equal To" step.

The second filter step takes the current date and time then uses a "Less Than or Equal To" step.

Once the data passes through both separate filters, I get the result I want. I was hoping there was a "Between" filter.

Is there a better way to do it?

Best Answer

  • Henry Simms
    Henry Simms Administrator
    Answer ✓

    Hi @George Brown

    There are a couple of different functions you might use here.

    The simplest is probably to use the Compare Date/Time function, using "Days" as the period type:

    image.png

    Using this function on 2022-08-17 will return:

    • For input: 01/01/2022, result: 228
    • For input: 12/08/2022, result: 5
    • For input 20/08/2022 (future date), result: -2

    Using this in a filter, you would use an If Then Else check on the result to only return true if between 0 and 7:

    image.png

    I'm defining "Days between dates" as a variable in the filter function so that I can reference it in the "Greater than..." and "Less than..." function blocks.

    Using the And logical operator you can combine any filter clauses into a single filter, so you could also use that approach with your existing functions.

    Once you've got the function working, you could parameterize the "last 7 days" part to "last x days" and make the function re-usable, allowing you to use it in future workflows without needing to re-create it:

    image.png

    I've attached an initial version of this as a re-usable function, created on v2.7.5 of Data Studio so only compatible with that version or higher.

    Data Studio Export - System - 17 Aug 2022 13-45-04-266.dmx


Answers

  • George Brown
    George Brown Member

    @Henry Simms , thanks for the suggestions and also posting the file. After I posted this question, I continued to work on it and came to a similar conclusion using the compare date/time but also using "In range".

    While this works for my single case, I certainly don't want to recreate every time, so I really appreciate your recommendations on how to make this a re-usable function. I'm going to take that route as I'm sure that other users will find value in this function as well.

  • Henry Simms
    Henry Simms Administrator
    edited August 2022

    Thanks George - Glad you figured it out. Reusable functions are very powerful. Head over to the Functions Library to see some functions that have been created by others, and which are available to download and use.

    Good tip on using In Range instead of Greater Than and Less Than, I'd forgotten about that one!

Categories