POP Beast Mode
Hoping someone can help me figure out why my POP Beast Mode calculation returns 0% in my charts. I want to compare current quarter to last quarter. I may also modify the calculation to compare last quarter to 2 quarters ago. I am only using two fields from my dataset, similiar to this table and my dates are always the 1st day of each month.
Month | Attributed Pipeline |
1/1/2018 | $1,000 |
2/1/2018 | $1,500 |
3/1/2018 | $6,000 |
4/1/2018 | $9,000 |
This is my Beast Mode calculation that returns 0% in my charts. I tried SUM before my case statement, but that did not change the results.
(CASE WHEN ((year(`Month`)=year(curdate())) AND (quarter(`Month`)=quarter(CURDATE()))) THEN `Attributed Pipeline` END / CASE WHEN (quarter(curdate())=1) THEN (CASE WHEN ((year(`Month`)=(year(curdate()) - 1)) AND (quarter(`Month`)=4)) THEN `Attributed Pipeline` END ) ELSE (CASE WHEN ((year(`Month`)=year(curdate())) AND (quarter(`Month`)=(quarter(CURDATE()) -1))) THEN `Attributed Pipeline` END ) END) -1
Thanks in advance for any help!!
Comments
-
At a high level your beast mode looks conceptually ok. What kind of chart are you using and where is the value showing up as 0?
Is that what your data actually looks like, or do you have more rows per month that would need to be summed up?
What I'd do is break it up by the components (numerator and denominator) and then see if those values come up as expected. Is your numerator (the sum of current quarter dollars) also zero? Get that number right, then move on to the denominator (previous month)
sum(
CASE
WHEN year(`Month`)=year(curdate()) AND quarter(`Month`)=quarter(CURDATE())
THEN `Attributed Pipeline`
END)
/
sum(
CASE
WHEN quarter(curdate())=1
THEN (CASE
WHEN year(`Month`)=year(curdate()) - 1 AND quarter(`Month`)=4
THEN `Attributed Pipeline`
END)
ELSE (CASE
WHEN year(`Month`)=year(curdate()) AND quarter(`Month`)=quarter(CURDATE()) -1
THEN `Attributed Pipeline`
END)
END)
-1Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
You could also display the POP veriance using the Period over Period Variance bar line card:
This way you wouldn't need any beast modes. This card was made from the table you posted
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 473 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 211 Visualize
- 257 Beast Mode
- 2.1K Charting
- 12 Variables
- 18 Automate
- 355 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
- 183 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive