% Change calculation for Year Over Year QTD doesn't seems to be working

user19019 · November 2020

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.

GrantSmith · November 2020

Hi @user19019

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

user19019 · November 2020

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?

jaeW_at_Onyx · November 2020

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!

Magic ETL

Magic ETL

% Change calculation for Year Over Year QTD doesn't seems to be working

Welcome!

Comments

Welcome!

Welcome!

Quick Links

Categories