Dynamic Date Range Comparison Using Variables

Options
Member

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!

Tagged:

• Member
Options

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.

• Coach
Options

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! **

• Member
Options

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.

• Coach
Options

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! **

• Member
Options

@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

• Member
edited July 24
Options

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?

• Coach
Options

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! **

• Member
Options

@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?

• Member
Options

@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`

• Coach
Options

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.

** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **

• Coach
Options

@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! **