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

jonathanlooker

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.

Godiepi

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

 

Godiepi

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

 

Unknown

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

jonathanlooker

Worked as intended as a filter, thank you very much

user09742

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