1st of the month with Current Month Filter

user083578 · April 2020

I'm trying to create a date filter that on the 1st of the month it shows previous month otherwise it's MTD. However, it must also allow for the date range to remain dynamic. The below code allows me to show MTD and on the 1st it will show last month. However, I lose all date range functionality. Does anyone have any suggestions or have run into this before? I'm sure there are more eloquent ways to write this but I'm more concerned about being able to keep the dynamic date ranges.

Thanks!

CASE 
     WHEN     DAYOFMONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) > 1 
          AND MONTH(`TransDate`) = MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) 
          AND YEAR(`TransDate`) = YEAR(CONVERT_TZ(NOW(),'UTC','America/New_York')) 
      THEN 1 

     WHEN     DAYOFMONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) = 1 
          AND MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) > 1 
          AND MONTH(`TransDate`) = MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York'))-1 
          AND YEAR(`TransDate`) = YEAR(CONVERT_TZ(NOW(),'UTC','America/New_York')) 
      THEN 1 

     WHEN     DAYOFMONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) = 1 
          AND MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) = 1 
          AND MONTH(`TransDate`) = MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York'))+11 
          AND YEAR(`TransDate`) = YEAR(CONVERT_TZ(NOW(),'UTC','America/New_York'))-1 
      THEN 1

     ELSE 0
 END

user083578 · April 2020

Thank you both for such quick responses. I wanted to test something yesterday and today before responding and it held up nicely. Below is the code that works for me. On the 1st it includes all of last month, then on the 2nd it moves to all of current month. Anything prior is then captured by month-year. Although you lose the date range filter capabilities, this still provides the option to select whichever month they want to look at.

Note: I used '00-Current Month' so it sits at the top of the quick filter list (see pic attached).

CASE 
    WHEN 
             DAY(CURRENT_DATE()) = 1 
         AND DATE_FORMAT(`TransDate`,'%m-%Y') = DATE_FORMAT(CURRENT_DATE()-1,'%m-%Y')  
     THEN '00-Current Month'

    WHEN 
             DAY(CURRENT_DATE()) != 1 
         AND DATE_FORMAT(`TransDate`,'%m-%Y') = DATE_FORMAT(CURRENT_DATE(),'%m-%Y')  
     THEN '00-Current Month'

    ELSE DATE_FORMAT(`TransDate`,'%m-%Y')
END

jaeW_at_Onyx · April 2020

sorry ... what do you mean by 'dynamic date ranges'?

in what context are you applying this?

my standard response when users are trying to build heavy calcs into their beast modes or ETLs is to say, build a date dimension ( a table with one row since the dawn of time and add attribute columns like year and month, and in your case, for a given date, how you want it to filter on it.

MarkSnodgrass · April 2020

The date filter doesn't lend itself to do this, but you can pull this off and keep it dynamic by making a beast mode that use as a quick filter. Here is the formula to create:

/*check to see if it is the 1st of the month */
(CASE when DAY(CURRENT_DATE()) = 1 then
 	/* it is the 1st of the month so only include data from the previous month */
   (CASE when MONTH(`date`) = MONTH(CURRENT_DATE())-1 then 'Include'
    /* exclude all other records */
    else 'Exclude'
    end)
else
 	/* it is not the 1st of the month so include the current month data */
    (CASE when MONTH(`date`) = MONTH(CURRENT_DATE()) then 'Include'
     /* exclude all other records */
    else 'Exclude'
    end)          
end)

Name this beast mode whatever you like and then drag it into the quick filters and then select Include. This will make it work for you for any time going forward.

Hope this helps.

user083578 · April 2020

Thank you both for such quick responses. I wanted to test something yesterday and today before responding and it held up nicely. Below is the code that works for me. On the 1st it includes all of last month, then on the 2nd it moves to all of current month. Anything prior is then captured by month-year. Although you lose the date range filter capabilities, this still provides the option to select whichever month they want to look at.

Note: I used '00-Current Month' so it sits at the top of the quick filter list (see pic attached).

CASE 
    WHEN 
             DAY(CURRENT_DATE()) = 1 
         AND DATE_FORMAT(`TransDate`,'%m-%Y') = DATE_FORMAT(CURRENT_DATE()-1,'%m-%Y')  
     THEN '00-Current Month'

    WHEN 
             DAY(CURRENT_DATE()) != 1 
         AND DATE_FORMAT(`TransDate`,'%m-%Y') = DATE_FORMAT(CURRENT_DATE(),'%m-%Y')  
     THEN '00-Current Month'

    ELSE DATE_FORMAT(`TransDate`,'%m-%Y')
END

JHoffman · April 2021

I think I've found a simple and elegant solution to this problem (in most cases) by creating a new beast mode date field based on the original date field in my dataset:

CASE
    WHEN DAY(CURRENT_DATE) = 1 THEN DATE_ADD(`date`, INTERVAL 1 MONTH)
    ELSE `date`
END

Using this beast mode date field in a card date range filter allows you to show the previous month's data on the first of the month and the current month's data on all other days. It even works with date range comparisons and allows users to select a date range dynamically when viewing the card in full screen mode.

The downside is that it's misleading for time series cards (or any card where you actually need to display the date) on the first of the month because you've altered the date values. Ideally I want to be able to use the original date field as the X Axis on a line chart for example, but use the beast mode date field in the date range filter. Unfortunately it seems like the date range filter can only be applied to the field used on the X Axis.

1st of the month with Current Month Filter

Best Answer

Answers

Categories