Dynamic Date Range Comparison Using Variables

Eric.P

Hi there,

I recently saw this video on DOMO Variables from Zack Cameron and around the 17:30 mark he's using variables to control the Time Range (YYD,QTD,MTD,WTD) and Time Comparison Option (YoY, PoP).

This functionality would be perfect for the dashboard I'm building out, but I'm having trouble recreating it. The code for the second beast mode calculation is cut off and I don't think I completed it correctly and I'm wondering how he set up his visual with the given fields. Anyone know how this was done?

Thanks!

https://www.youtube.com/watch?v=jZBPXQ_AHpY&ab_channel=Domo

jrudd

@Eric.P

I have built out an example of this that will hopefully help you. Below I have included a screenshot of the card that I built as well as the test dataset. I have also included Word docs for the 2 beast modes. This isn't an exact replica but should serve the same purpose. Let me know if you have any questions on what I have put together.

Current Time Range.doc

Comparison Time Range.doc

ArborRose

Current year would be everything where the year({your date}) matches year() of current date. Previous year up through the same day of the year would be similar but you subtract 1 from the year and need to add a qualifier where {yourdate} must be less than one year prior.

So CY and CTD (current year to date) are the same. But PY and PYTD would differ in having that second condition.

CY:

SUM(CASE WHEN YEAR(`date`) = YEAR(CURDATE())

    THEN `amount` 

    ELSE 0 

    END)


PY:

SUM(CASE

    WHEN YEAR(`date`) = YEAR(CURDATE()) - 1

    AND `date` < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

    THEN `amount`

    ELSE 0

END)

If you need QTD, you would add a qualifier for QUARTER().

jrudd

@Eric.P

I have built out an example of this that will hopefully help you. Below I have included a screenshot of the card that I built as well as the test dataset. I have also included Word docs for the 2 beast modes. This isn't an exact replica but should serve the same purpose. Let me know if you have any questions on what I have put together.

Current Time Range.doc

Comparison Time Range.doc

ArborRose

Current year would be everything where the year({your date}) matches year() of current date. Previous year up through the same day of the year would be similar but you subtract 1 from the year and need to add a qualifier where {yourdate} must be less than one year prior.

So CY and CTD (current year to date) are the same. But PY and PYTD would differ in having that second condition.

CY:

SUM(CASE WHEN YEAR(`date`) = YEAR(CURDATE())

    THEN `amount` 

    ELSE 0 

    END)


PY:

SUM(CASE

    WHEN YEAR(`date`) = YEAR(CURDATE()) - 1

    AND `date` < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

    THEN `amount`

    ELSE 0

END)

If you need QTD, you would add a qualifier for QUARTER().

Eric.P

@jrudd That wouldn't be YTD though, right? You're only getting from July 15th 2023 onwards, so you're not able to compare the beginning part of the previous year to this one. I've tweaked the code some so I'm able to properly capture the YTD YoY data, but outside of that I still haven't had any luck with other periods

Comparison Time Range.docx

jrudd

@Eric.P

I went back and added Jan-Jun 2023 data into my dataset and it is working as intended. My YTD Time Range with a YoY comparison pulls all of the 2023 data and compares it to all of the 2024 data for each region.

For YoY comparisons it will compare this year to last year (YTD), current quarter to the same quarter last year, this month to the same month last year, etc.

For PoP comparisons it will compare this year to last year, current quarter to last quarter (current year), current month compared to last month (current year), etc.

If you needed more specific comparisons based on the current date I am sure we could work through that. Could you provide a sample of how your data is setup with date so that I could compare?

ArborRose

If your date is less than the current date in the current year, it's YTD. If you do a previous year, you have to include the statement date less then the date minus a year, which is the comparison date for previous year.

You can subtract an interval of days or subtract an interval of year.

SUM(

CASE WHEN YEAR(`date`) = YEAR(DATE_ADD(CURRENT_DATE(),-365))

AND `date` <= DATE_ADD(CURRENT_DATE(),-365)

THEN `amount`

ELSE 0

END)

)





SUM(CASE

WHEN YEAR(`date`) = YEAR(CURDATE()) - 1

AND `date` < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

THEN `amount`

ELSE 0

END)

Eric.P

@jrudd Yes, my fields are Country, event_date (Jan 1, 2023), revenue, and total_transactions. Do I need to extract the quarter, week and period data in my dataset?

Eric.P

@ArborRose I tried using that logic on mine but still am not seeing proper PoP numbers

CASE


WHEN Time Comparison = 'YoY' THEN


CASE
WHEN Time Range = 'YTD' THEN


SUM(


CASE
WHEN YEAR(event_date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR) AND event_date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)


THEN transactions
ELSE 0
END
)


WHEN Time Range = 'QTD' THEN


SUM(


CASE
WHEN YEAR(event_date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)) AND QUARTER(event_date) = QUARTER(DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)) AND event_date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)


THEN transactions


ELSE 0
END
)


WHEN Time Range = 'MTD' THEN


SUM(


CASE
WHEN YEAR(event_date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)) AND MONTH(event_date) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)) AND event_date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)


THEN transactions


ELSE 0
END
)


WHEN Time Range = 'WTD' THEN


SUM(


CASE
WHEN YEAR(event_date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)) AND WEEK(event_date) = WEEK(DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)) AND event_date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)


THEN transactions
ELSE 0
END
)


END


WHEN Time Comparison = 'PoP' THEN


CASE
WHEN Time Range = 'YTD' THEN


SUM(


CASE
WHEN YEAR(event_date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)) AND event_date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)


THEN transactions
ELSE 0
END
)


WHEN Time Range = 'QTD' THEN


SUM(


CASE
WHEN YEAR(event_date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL -1 QUARTER)) AND QUARTER(event_date) = QUARTER(DATE_ADD(CURRENT_DATE(), INTERVAL -1 QUARTER)) AND event_date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 QUARTER)


THEN transactions
ELSE 0
END
)


WHEN Time Range = 'MTD' THEN


SUM(


CASE
WHEN YEAR(event_date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)) AND MONTH(event_date) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)) AND event_date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)


THEN transactions
ELSE 0
END
)


WHEN Time Range = 'WTD' THEN


SUM(


CASE
WHEN YEAR(event_date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL -1 WEEK)) AND WEEK(event_date) = WEEK(DATE_ADD(CURRENT_DATE(), INTERVAL -1 WEEK)) AND event_date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 WEEK)


THEN transactions
ELSE 0
END
)


END


END

ArborRose

If you are looking to compare quarters from current year to previous year, you would have something using Quarter().

SUM(
CASE WHEN YEAR(event_date) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
AND QUARTER(event_date) = QUARTER(DATE_ADD(CURRENT_DATE(),-365))
AND event_date <= DATE_ADD(CURRENT_DATE(),-365)
THEN amount
ELSE 0
END)
)

If you want to compare each quarter as opposed to the current quarter, then it would be something like quarter(`event_date`) = '2'. Where the numeric is the quarter (1,2,3, or 4) of the year.

ArborRose

@Eric.P - I see your question. But I have to join a conference call for a bit. I'll circle around when I can and clarify with an example of how I implement comparisons. Someone else viewing may also jump in and explain.