POP Beast Mode

Reply
Visitor

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.

 

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

Highlighted
Red Belt
Red Belt

Re: POP Beast Mode

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)


-1
Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Red Belt

Re: POP Beast Mode

You could also display the POP veriance using the Period over Period Variance bar line card:

1.png

 

 

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.” -Superman
______________________________________________________________________________________________
Announcements
September release notes Check out the details of our latest product release. Click here!