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

jonathanlooker
jonathanlooker 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.

Best Answer

  • Godiepi
    Godiepi 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 :

    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

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

    As of Yesterday:

    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

  • Godiepi
    Godiepi 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 :

    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

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

    As of Yesterday:

    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?

  • jonathanlooker
    jonathanlooker Member

    Worked as intended as a filter, thank you very much

  • user09742
    user09742 Member

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

Categories