# Dividing with Multiple Beast Mode Functions

I have 2 formulas I am trying to divide into one another

CASE

WHEN `Action` = 'Lead' THEN SUM(`Unique Events`)

END

and

CASE

WHEN `Action` = 'Completion' THEN SUM(`Unique Events`)

END

Individually they work fine and result in 100000 and 50000 respectively. I need to divide Completions into Leads to determine my dropoff rate (50000/100000). Can someone please help me to divide these 2 Beast Mode functions?

Tagged:

• Hi,

SUM(CASE

WHEN `Action` = 'Lead' THEN `Unique Events`

END)

/

SUM(CASE

WHEN `Action` = 'Completion' THEN `Unique Events`

END)

So basically, type the first part but aggregate the entire case statement instead of aggregating it inside the case. Then divide it by the second formula, here also, please aggregate the entire case statement and not the inner 'then' or 'else' condition, just as I have shown in the above formula.
Also it's advised to use the aggregation function outside the case statement to avoid erros in aggregation.

Hope this helps

Regards

The function returns only blank rows now. Do you have any alternatives or edits to the function?

If not are there any alternative solutions you can recommend? I am just trying to divide among filtered aggregates.

• Hi,

The reason for it to return blank rows could be,

1. There could be a spelling error in the 'WHEN' condition, so kindly check the spelling (it's case sensitive too).
2. The condition is never true and since there is no ELSE condition, it would return null, so kindly add ELSE 0 in both the condition or give an appropriate ELSE condition if the WHEN is turning out to be false
for example

SUM(CASE

WHEN `Action` = 'Lead' THEN `Unique Events` ELSE 0

END)

/

SUM(CASE

WHEN `Action` = 'Completion' THEN `Unique Events` ELSE 0

END)

3. There could be null values in your columns you are using as the numerator and denominator. In that case, you can use IFNULL function to handle null by converting the null to 0. for example,

SUM(IFNULL(CASE

WHEN `Action` = 'Lead' THEN `Unique Events`

END,0))

/

SUM(IFNULL(CASE

WHEN `Action` = 'Completion' THEN `Unique Events`

END,0))

Do let me know if this helps.

Regards

Those recommendations did get data into my columns, but they were still 0 unfortunately.

At this point, is Magic ETL a better option? I am not sure what the issue could be otherwise since I am pulling this data directly from analytics.

• Hi @user022663 ,

If you are still getting 0, then it could be because the numerator part of the formula is 0. So I think you should check the data for the numerator, i.e., 'Action' = 'Lead' part.. Also, please check if you are using your column 'Action' as a filter and you have not put 'Action' = 'Completion' as a filter. I think you are almost there to the solution

Yes, ETL is another option, but data pivoting will be required while this problem is not a big issue so beast modes are sufficient for such issues.
We can try ETL way as well, but for any suggestions, some more info regarding the data is needed which you  may not be comfortable to share. If that's not the case you can inbox the sample data and we can solve this.

Hope this helps.

Regards