Skip to main content
Question

Hi All,Need to apply filter while extracting the data to have rolling month/year data. For example the first day of extraction of data is 01/12/2022 to till now then after a month data should be extracted from 01/01/2023 to till now.

  • December 7, 2023
  • 1 reply
  • 39 views
A
Level 4

Tried applying the filter statement in one of the filters of table but it didn't support the sql query. Any guidance would be really helpful.

    1 reply

    dennis.pflug
    Level 10
    • dennis.pflug
    • Level 10
    • December 7, 2023

    Hi,

     

    you can do it like this

     

    FILTER <your_date_field> BETWEEN ADD_MONTHS(ROUND_MONTH(TODAY()),-12) AND TODAY()

     

    But I think you will do it per full months so maybe setting the timeframe to today is not ideal because the filter now would be:

     

    01.12.2022 - 07.12.2023.

     

    So you can use similar logic to your end date like:

    FILTER <your_date_field> BETWEEN ADD_MONTHS(ROUND_MONTH(TODAY()),-12) AND ADD_SECONDS(ROUND_MONTH(TODAY()),-1)

     

    This results in 01.12.2022 00:00:00 - 30.11.2023 23:59:59

     

    BR

    Dennis

     

    Community Code of ConductAccessibility statement