Error in DOMO documentation - POP: QTD Quarter-over-Quarter Comparison
Hi,
I was trying to use Beast Mode that is provided here: https://domohelp.domo.com/hc/en-us/articles/360043430133-Sample-Beast-Mode-Calculations-Period-over-Period-Transforms#7.2., however the formula is not valid for Multiple Series - Previous Period. The error comes from additional ")" bracket that doesn't have corresponding opening bracket. If I remove the ")", the formula is valid, but the numbers are all wrong. I tried to place the opening "(" in few different places, but with no success.
Can you please advise how can I fix the formula?
Comments
-
Have you tried breaking this down and start with a table card and then create a beast mode for each of the functions and see what they return in the table card and then building it up from there? That should reveal where the error in the calculation is.
You might also want to look at a nice write-up @GrantSmith did on a flexible way to do period over period comparisons. https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.3 -
For anyone facing this problem, here's a solution. My colleague fixed and simplified the formula:
CASE
When
(
-- Check Date is in previous quarter
CASE
When QUARTER(CURDATE()) = 1 and (QUARTER(`Datefield`) = 4 and (YEAR(`Datefield`) = YEAR(CURDATE())-1))
or
((QUARTER(CURDATE())-1 = QUARTER(`Datefield`)) and (YEAR(`Datefield`) = YEAR(CURDATE()))) then 'T'
END
) = 'T'
AND
-- check days in Q are equal
-- days in CQ
(
-- Days from start of quarter to Current date
DATEDIFF( CURDATE(),
CASE
When QUARTER(CURDATE()) = 1 then CONCAT(YEAR(CURDATE()), '-01-01')
When QUARTER(CURDATE()) = 2 then CONCAT(YEAR(CURDATE()), '-04-01')
When QUARTER(CURDATE()) = 3 then CONCAT(YEAR(CURDATE()), '-07-01')
When QUARTER(CURDATE()) = 4 then CONCAT(YEAR(CURDATE()), '-10-01')
END
)
) -- current Q days is >= Date Q days
>=
-- get Date Q days
-- Days from start of Date field quarter to date
(
DATEDIFF( `Datefield`,
CASE
When QUARTER(`Datefield`) = 1 then CONCAT(YEAR(`Datefield`), '-01-01')
When QUARTER(`Datefield`) = 2 then CONCAT(YEAR(`Datefield`), '-04-01')
When QUARTER(`Datefield`) = 3 then CONCAT(YEAR(`Datefield`), '-07-01')
When QUARTER(`Datefield`) = 4 then CONCAT(YEAR(`Datefield`), '-10-01')
END
)
)
then `Amountfield`
END
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 754 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive