How to deal with the 1st of month for "Current month" filter
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
-
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.
0
Answers
-
Hello Superman (
),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 !
0
