% Change calculation for Year Over Year QTD doesn't seems to be working
I am trying create a table with multiple segments as row and Current Year QTD Revenue, Prev Year QTD Revenue and % Change in Revenue.
I was able to follow the documentation (https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/Sample_Beast_Mode_Calculations%3A_Period-over-Period_Transforms)
and Implement beast modes for Current Year QTD Revenue, Prev Year QTD Revenue.
But when I implement
|Ratio: % change between the two using the formula (C/P) - 1|
(Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate()) and dayofyear(`Datefield`) <= dayofyear(curdate())) then `Amountfield` end / Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())-1 and dayofyear(`Datefield`) <= dayofyear(curdate())) then`Amountfield` end) -1
It doesn't seems to work and am getting blank as % change. Any help would be appreciated.
Note: My data has multiple columns for segmentation and I want to use these 3 beast modes for different segments.
There's several examples of doing period over period charts on the dojo. I'd recommend you configure an offset dimensions table to join your data together. @jaeW_at_Onyx has done a great video outlining this process here: https://www.youtube.com/watch?v=CDKNOmKClms
I've given a more textual write up on this here: https://dojo.domo.com/t5/Card-Building/show-percent-on-period-over-period-graph/m-p/50540/highlight/true#M7313**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
I referred the manual writeup .
You are doing Last Quarter Calculation
select d.`dt` as "Report Date", date_sub(`dt`, interval '1' quarter) as "Comparison Date", 'Last Quarter' as 'Period Type'
from `calendar_dates` d
How would you do Current Quarter to date and Previous Year Quarter to Date?0
i hope you solved your problem!
... If you followed the tutorial video Grant posted, (thanks @GrantSmith ) the solution should just be a matter of wrangling MySQL code ... this is a VERY google-able issue. But basically you just need to append a Current QTD and a previous QTD Period Type to the OFFSET_DATE table and then build some beast modes
sum( case when Period type = ...) - sum( case when period type = ... )
If you're looking for people to write code for you, the Dojo does have a Job Board where freelance consultants, https://dojo.domo.com/t5/Job-Board/Freelance-Domo-consultant-available-for-hourly-and-long-term/m-p/48595#M44, are available to do projects for you on an hourly rate!Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 473 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 203 Visualize
- 255 Beast Mode
- 2.1K Charting
- 12 Variables
- 84 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 182 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive