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