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
Blue 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.

All Replies
Blue 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.