Dividing with Multiple Beast Mode Functions

user022663

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?

Aditya_Jain

Hi,

Please try this ,

 

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

Aditya Jain

user022663

Thanks Aditya,

 

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.

Aditya_Jain

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
   Aditya Jain

user022663

Thanks Again Aditya,

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.

Aditya_Jain

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

Aditya Jain 

GrantSmith

@user022663 - Are you formatting the percentage correctly? If it's displaying a number and it's under 50% (0.5)  it'll round down being displayed as 0. Make sure your field is being formatted as a percentage and check the multiply by 100 box (and even add a decimal point or two)