Dynamic Date Range Comparison Using Variables
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!
Best Answers
-
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.
0 -
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().
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
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.
0 -
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().
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@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
0 -
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?
0 -
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)** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@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' THENSUM(
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' THENSUM(
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
0 -
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))
ANDevent_date
<= DATE_ADD(CURRENT_DATE(),-365)
THENamount
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.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@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.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive