Reply
Highlighted
Visitor
Posts: 2
Registered: ‎04-28-2017
Accepted Solution

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
Solution
Accepted by topic author karenbrar
‎05-11-2017 11:51 AM
Green Belt
Posts: 58
Registered: ‎04-19-2017

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'

View solution in original post


All Replies
Solution
Accepted by topic author karenbrar
‎05-11-2017 11:51 AM
Green Belt
Posts: 58
Registered: ‎04-19-2017

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
Posts: 2
Registered: ‎04-28-2017

Re: Beastmode Percentage Calculation

This works perfectly, thanks! 

Announcements
Customize your Dojo Handle! You can update your Dojo screen name by clicking on My Settings. click here! Thanks!