How to deal with the 1st of month for "Current month" filter

user09465
user09465 Member
in Magic ETL

Hello all,

 

Context : Each day, we feed DOMO with the data of the day before (for exemple today, the 1st of August, I have data until 31st of July). In some reports, I put only cards with "current month" filter to see the evolution through the month and I allow users to see the date filter in order to interact with the data.

 

Problem : Today, all the cards of my report are empty because I don't have data for August ! Yet, it would be very interesting to have the vision of the complete month... 

How can I take the previous month filter only for the 1st of month ?

 

I've tried to think about solution but without result... I hope you will have an idea !

 

Thank you

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    Rather than using the "Current Month" filter on the date field, you could use a beastmode to filter the dates on the card:

     

    case

    when year(DATE_SUB(curdate(), interval 1 day)!= year(curdate()) and 

    year(`date_field`)=year(curdate())-1 and month(`date_field`)=12 then 'Y'

    when (case when day(curdate())=1 then month(DATE_SUB(curdate(), interval 1 day)) else month(curdate()) end) = month(`date_field`)  and year(`date_field`)=year(curdate()) then 'Y'

     

    else 'N'

    end

     

    You would then use this beast mode as a filter.  Selecting Y would give you the Current month's data, unless it was the 1st of the month.  On the 1st of the month, it would give you the previous month's data.

     

    There may be a cleaner approach to this, but I had to make a special case for the first day of the year, so that the card would know to grab the data from December of the previous year on that day.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    Rather than using the "Current Month" filter on the date field, you could use a beastmode to filter the dates on the card:

     

    case

    when year(DATE_SUB(curdate(), interval 1 day)!= year(curdate()) and 

    year(`date_field`)=year(curdate())-1 and month(`date_field`)=12 then 'Y'

    when (case when day(curdate())=1 then month(DATE_SUB(curdate(), interval 1 day)) else month(curdate()) end) = month(`date_field`)  and year(`date_field`)=year(curdate()) then 'Y'

     

    else 'N'

    end

     

    You would then use this beast mode as a filter.  Selecting Y would give you the Current month's data, unless it was the 1st of the month.  On the 1st of the month, it would give you the previous month's data.

     

    There may be a cleaner approach to this, but I had to make a special case for the first day of the year, so that the card would know to grab the data from December of the previous year on that day.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user09465
    user09465 Member

    Hello Superman ( Smiley Very Happy ),

     

    Thank you for this solution, I will adapt and test it to offer more periods because it was very important for me to give users the opportunity to change the observation period (yesterday, last month, last year etc)

     

    Have a good day !

Categories