Help with Date Range/ Period to Date

OllyB
OllyB Member
edited December 5 in Beast Mode

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?

Tagged:

Best Answer

  • ggenovese
    ggenovese Contributor
    edited December 3 Answer ✓

    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

Answers

  • ggenovese
    ggenovese Contributor
    edited December 3 Answer ✓

    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

Categories