Skip to content

    • Magic ETL

    Magic ETL

    Exclude all data from Current Month (in either Beast Mode or ETL)

    Member
    in Magic ETL

    My aim is to exclude all current month data.

     

    I have tried to do this in ETL, however the date field filter doesn't appear to allow for "less than current month" - happy to take suggestions.

     

    I have also tried in Beast Mode formula. I have successfully excluded data in other calculations using currentdate however am struggling to retain all data except for the current month.

     

    Ideas very welcom, thanks.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      Hi Jonathan,

       

      I normally use beastmodes to apply as filters in the cards we build so that they don't show partial month, week or day data.

       

      Notice I use `TransactionDate` as my field driver and it is present in my data set (you can replace it with your field name)

       

      These are as follows:

       

      As of Last Month :

      1. CASE 
           WHEN (YEAR(`TransactionDate`)*100)+MONTH(`TransactionDate`) 
                 >= (YEAR(CURRENT_DATE())*100)+MONTH(CURRENT_DATE()) 
           THEN 'NO' 
        ELSE 'YES' END

      As of Last Week:

      on this one, the number 11 in the YEARWEEK function means our weeks go from sunday to saturday

      1. (CASE 
        WHEN ((YEAR(`TransactionDate`)*100) + YEARWEEK(`TransactionDate`, 11)) 
            < 
        ((YEAR(CURRENT_DATE())*100) + YEARWEEK(CURRENT_DATE(), 11)) 
        THEN 'YES'
         ELSE 'NO' END)

      As of Yesterday:

      1. CASE
        WHEN `TransactionDate` = CURRENT_DATE() 
        THEN 'No'
        ELSE 'Yes' END

      Hope this helps you

       

      Domo Arigato!

      **Say 'Thanks' by clicking the thumbs up in the post that helped you.
      **Please mark the post that solves your problem as 'Accepted Solution'

    Answers

    • Coach
      Answer ✓

      Hi Jonathan,

       

      I normally use beastmodes to apply as filters in the cards we build so that they don't show partial month, week or day data.

       

      Notice I use `TransactionDate` as my field driver and it is present in my data set (you can replace it with your field name)

       

      These are as follows:

       

      As of Last Month :

      1. CASE 
           WHEN (YEAR(`TransactionDate`)*100)+MONTH(`TransactionDate`) 
                 >= (YEAR(CURRENT_DATE())*100)+MONTH(CURRENT_DATE()) 
           THEN 'NO' 
        ELSE 'YES' END

      As of Last Week:

      on this one, the number 11 in the YEARWEEK function means our weeks go from sunday to saturday

      1. (CASE 
        WHEN ((YEAR(`TransactionDate`)*100) + YEARWEEK(`TransactionDate`, 11)) 
            < 
        ((YEAR(CURRENT_DATE())*100) + YEARWEEK(CURRENT_DATE(), 11)) 
        THEN 'YES'
         ELSE 'NO' END)

      As of Yesterday:

      1. CASE
        WHEN `TransactionDate` = CURRENT_DATE() 
        THEN 'No'
        ELSE 'Yes' END

      Hope this helps you

       

      Domo Arigato!

      **Say 'Thanks' by clicking the thumbs up in the post that helped you.
      **Please mark the post that solves your problem as 'Accepted Solution'
    • Unknown
      Unknown

      @jonathanlooker, did @Godiepi's reply help you out?

    • Member

      Worked as intended as a filter, thank you very much

    • Member

      If the graph showing the past 3 months data, but I want to exclude some data from the current month?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories