Beastmode Percentage Calculation

Reply
Highlighted
Visitor

Beastmode Percentage Calculation

I am trying to calculate a ratio by doing the following: 

 

CASE WHEN `Subscription_Count` = 0 or `Subscription_Count` is null THEN 0
ELSE
(SUM(`Tickets`)
/
SUM(`Subscription_Count`)*100)
END

 

However, when I try to display this on a line (Ratio) + Bar graph(Subscription_Count & Tickets) , I get the following message :"Preview could not be loaded".

 

If I take out the SUM in the beast mode calculation, then it sums all the ratios for the given date on the x-axis. 

 

Is there any way around this?

 

Thanks.


Accepted Solutions
Purple Belt

Re: Beastmode Percentage Calculation

Hi Karen!

 

Beast mode doesn't play too well with aggregates inside the case statements. What you'll want to do is nest the case statements inside the aggregates:

 

Orignal:

 

 

CASE WHEN `Subscription_Count` = 0 or `Subscription_Count` is null THEN 0
ELSE
(SUM(`Tickets`)
/
SUM(`Subscription_Count`)*100)
END

 

Updated:

 

(SUM(CASE WHEN `Subscription_Count` = 0 or `Subscription_Count` is null THEN 0
ELSE `Tickets` END))

 

/

 

(SUM(`Subscription_Count)*100)

 

 

Try this and see if it works!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'

All Replies
Purple Belt

Re: Beastmode Percentage Calculation

Hi Karen!

 

Beast mode doesn't play too well with aggregates inside the case statements. What you'll want to do is nest the case statements inside the aggregates:

 

Orignal:

 

 

CASE WHEN `Subscription_Count` = 0 or `Subscription_Count` is null THEN 0
ELSE
(SUM(`Tickets`)
/
SUM(`Subscription_Count`)*100)
END

 

Updated:

 

(SUM(CASE WHEN `Subscription_Count` = 0 or `Subscription_Count` is null THEN 0
ELSE `Tickets` END))

 

/

 

(SUM(`Subscription_Count)*100)

 

 

Try this and see if it works!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Visitor

Re: Beastmode Percentage Calculation

This works perfectly, thanks! 

Announcements
New Dojo experience is here! Here are some quick tips, we would also love your feedback. Click here!