Help with Date Range/ Period to Date

OllyB · 2024-12-03T11:03:13+00:00

There was an error rendering this rich post.

As you know Domo has a selection Date Range, Period to Date, and if I click on Week to Date, my 3 cards will update the date dimension to current Week to Date, Month to Date, and Quarter to Date.

I'm trying to create a filter or a button to do same functions Period to Date: WTD, MTD, QTD, YTD without clicking on Date Range, Current. The reason for this filter is I can place it on the main dashboard and my stakeholders can easily see WTD, MTD buttons and they can select Period to Date. Any suggestions?

Find more posts tagged with

Date

Accepted answers

ggenovese

Anything you create manually will not interact with the Date Range filter, keep that in mind because it could cause issues. It might be helpful to uncheck "Allow Global Date" filtering on your cards.

If you go this route, I would recommend creating a variable and then have a beast mode that uses the variable and then place the beast mode in your filters. Example:

CASE 
WHEN `Period Variable` = 'WTD' 
AND `Date` >= DATE_SUB(TODAY(), INTERVAL (DAYOFWEEK(TODAY()) - 1) DAY) 
AND `Date` ⇐ TODAY()THEN 'Include'
WHEN `Period Variable` = 'MTD' 
AND `Date` >= DATE_SUB(TODAY(), INTERVAL (DAY(TODAY()) - 1) DAY) 
AND `Date` ⇐ TODAY()THEN 'Include'
WHEN `Period Variable` = 'QTD' 
AND `Date` >= DATE_SUB(DATE_SUB(TODAY(), INTERVAL (DAY(TODAY()) - 1) DAY) , INTERVAL (MOD(MONTH(TODAY()) + 2, 3)) MONTH)
AND `Date` ⇐ TODAY()THEN 'Include'
WHEN `Period Variable` = 'YTD' 
AND `Date` >= STR_TO_DATE(CONCAT('01-01',YEAR(TODAY)),'%Y-%m-%d')
AND `Date` ⇐ TODAY()THEN 'Include'
ELSE 'Exclude'
END

All comments

ggenovese

Anything you create manually will not interact with the Date Range filter, keep that in mind because it could cause issues. It might be helpful to uncheck "Allow Global Date" filtering on your cards.

If you go this route, I would recommend creating a variable and then have a beast mode that uses the variable and then place the beast mode in your filters. Example:

CASE 
WHEN `Period Variable` = 'WTD' 
AND `Date` >= DATE_SUB(TODAY(), INTERVAL (DAYOFWEEK(TODAY()) - 1) DAY) 
AND `Date` ⇐ TODAY()THEN 'Include'
WHEN `Period Variable` = 'MTD' 
AND `Date` >= DATE_SUB(TODAY(), INTERVAL (DAY(TODAY()) - 1) DAY) 
AND `Date` ⇐ TODAY()THEN 'Include'
WHEN `Period Variable` = 'QTD' 
AND `Date` >= DATE_SUB(DATE_SUB(TODAY(), INTERVAL (DAY(TODAY()) - 1) DAY) , INTERVAL (MOD(MONTH(TODAY()) + 2, 3)) MONTH)
AND `Date` ⇐ TODAY()THEN 'Include'
WHEN `Period Variable` = 'YTD' 
AND `Date` >= STR_TO_DATE(CONCAT('01-01',YEAR(TODAY)),'%Y-%m-%d')
AND `Date` ⇐ TODAY()THEN 'Include'
ELSE 'Exclude'
END

OllyB

@g g e n o v e s e
Anything you create manually will not interact with the Date Range filter, keep that in mind because it could cause issues. It might be helpful to uncheck "Allow Global Date" filtering on your cards.
If you go this route, I would recommend creating a variable and then have a beast mode that uses the variable and then place the beast mode in your filters. Example:

It's better late than never - thank you! 😃