Compare against a specific date?
I have a dataset that aggregates data on a daily basis. I would like to compare the most recent value to the value on a specific date.
Example:
Date Value
1/1/24 400
1/2/24 403
1/3/24 410
….
5/7/24 500
In my Multi Value card, I am using MAX of Date and Value to pull the value 500, then for comparison, I have the all the date options (days, Quarters, Years, etc…)
I want to be able to get a % change between 5/7/24 and 1/1/24 (IE % change year to date).
Any ideas?
Best Answer
-
@mberkeley you can use the "Multi-value columns" comparative card.
Then use the first part of the previous beast mode to calculate the current value
AVG(case when most_recent_flag = 1 then value end)
And the second part to calculate the comparison value
AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end)
Then set it up like this.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0
Answers
-
@mberkeley my suggested approach would be…
Step 1: In your ETL, use the rank and window tile to assign the most recent data a value of 1. This will be used to filter in a beast mode. Say we call this MOST_RECENT_FLAG
Step 2: Create your beast mode. You can set your comparison date by adjusting the values in the STR_TO_DATE function.
(AVG(case when most_recent_flag = 1 then value end) AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end))
/
AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end)You can see that this allows us to calculate a YTD change with the most recent value. In this example dataset I used, the last date with values is March 31st.
If this answers your question, please 'like/heart' the response and then 'accept' as the answer.
Happy to answer any questions you may have 😁
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
I have given this example in previous answers. Say you create a formula for the current year. You can use the same formula to also find the value for the year before {previous year}. To find the difference, just subtract one from the other.
This should be the same thing you are working with. The difference being, you want to compare to a static date.Current Year (YTD):
sum(
case when YEAR(mydate) = YEAR(CURRENT_DATE())
and mydate <= CURRENT_DATE()
then amount else 0 end
)
Previous Year (YTD):
sum(
case when YEAR(mydate) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and mydate <= DATE_ADD(CURRENT_DATE(),-365)
then amount else 0 end
)
Difference between CY and PY:
sum(
case when YEAR(mydate) = YEAR(CURRENT_DATE())
and mydate <= CURRENT_DATE()
then amount else 0 end
)-
sum(
case when YEAR(mydate) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and mydate <= DATE_ADD(CURRENT_DATE(),-365)
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 -
@david_cunningham Thanks. I am able to get the values for the dates and do the math as you suggested. What I can't figure out is how to use the multivalue card to show the Current Value and then the % change. like this:
0 -
@mberkeley you can use the "Multi-value columns" comparative card.
Then use the first part of the previous beast mode to calculate the current value
AVG(case when most_recent_flag = 1 then value end)
And the second part to calculate the comparison value
AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end)
Then set it up like this.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
@david_cunningham That did it. It would help if I used the right chart type 😁
1
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Connect
- 1.3K Connectors
- 309 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 663 Datasets
- 119 SQL DataFlows
- 2.3K Magic ETL
- 823 Beast Mode
- Visualize
- 2.6K Charting
- 86 App Studio
- 46 Variables
- Automate
- 193 Apps
- 483 APIs & Domo Developer
- 87 Workflows
- 23 Code Engine
- AI and Machine Learning
- 23 AI Chat
- 4 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 117 Domo Everywhere
- 283 Scheduled Reports
- 11 Software Integrations
- Manage
- 143 Governance & Security
- 11 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 116 Community Announcements
- 5K Archive