How would I pull the highest rate over the last 12 months in a beastmode?

lb1234
lb1234 Member
in Datasets

In an effort to create a comparitive gauge chart looking at the current month's rate compared to the avg monthly rate over the last 12 months, the highest rate over the last 12 months and the lowest rate over the last 12 months. This is the beastmode I'm currently working with to attemp to get the max rate:

MAX(SUM((COUNT(DISTINCT CASE WHEN Task.EventOutcome = 'Converted' AND Created Date >= DATE_SUB(CURDATE(), INTERVAL 365 DAY) THEN Task.ActivityID END) OVER (PARTITION BY MONTH(Created Date)))

/

(COUNT(DISTINCT CASE WHEN Created Date >= DATE_SUB(CURDATE(),INTERVAL 365 DAY) THEN Task.ActivityID END ) OVER (PARTITION BY MONTH(Created Date)))))

Using this beast mode in a single value chart to see what it brings up, I'm finding that even though the beast mode editor has validated the beast mode, nothing is pulling up on the chart. Where am I going wrong here?

Tagged:

Answers

  • ST_Superman
    ST_Superman Domo Employee

    It's pretty difficult to tell where the error is here. I tend to try to break these complicated calculated fields up firts to make sure that I am getting the correct values for each "step". For example, make sure your numerator value is coming through correctly, then confirm the denominator value. Then put the whole thing together. That being said, you are doing a lot of aggregating here. Taking the Max of the Sum of the distinct count. Not sure how Domo will handle that. You could try this? 

    MAX(
    
SUM(
    
(COUNT(DISTINCT
    
CASE WHEN Task.EventOutcome = 'Converted' AND Created Date >= DATE_SUB(CURDATE(), INTERVAL 365 DAY) THEN Task.ActivityID END))) OVER (PARTITION BY MONTH(`Created Date`))
/
SUM(COUNT(DISTINCT
    
CASE WHEN Created Date >= DATE_SUB(CURDATE(),INTERVAL 365 DAY) THEN Task.ActivityID END ))
    
OVER (PARTITION BY MONTH(`Created Date`))
    
)
  • lb1234
    lb1234 Member

    Thank you for the attempt! Domo is still not liking it. I'll probably have to create it in an ETL.

Categories