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:

Comments

  • 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

    'Happy to Help'
  • 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.

  • 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
    'Happy to Help'
  • 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.

  • 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 

    'Happy to Help'
  • @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)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**