Issue with Beast Mode Calculation not displaying on the card.

Golfoholic

I am trying to find the conversion % for two different marketing sources. What % of those that inquire in either the walk in channel or the phone channel actually rented.

I wrote this beast mode calculation, but nothing is displaying:

SUM(CASE WHEN 'lead_source' = 'Walk_In' AND 'unit_rented_c' = 1 THEN 1 END)/SUM(CASE WHEN 'lead_source' = 'Walk_In' THEN 1 END)

Looking at the calculation beast mode isn't giving me a syntax error and the logic seems to make complete sense. What am I not accounting for?

GrantSmith

Hi @user048760

This is because you don't have an else clause so any record which doesn't have a Walk_In lead_source would return NULL which make your entire aggregate as NULL. Try adding an ELSE 0 clause to both of your numerator and denominator case statements.

SUM(CASE WHEN 'lead_source' = 'Walk_In' AND 'unit_rented_c' = 1 THEN 1 ELSE 0 END)/SUM(CASE WHEN 'lead_source' = 'Walk_In' THEN 1 ELSE 0 END)

jaeW_at_Onyx

@user048760 , FYI Beast modes are case sensitive, so check that you have case correct.

SUM(CASE WHEN `lead_source` = 'Walk_In' AND `unit_rented_c` = 1 THEN 1 END)
/
SUM(CASE WHEN `lead_source` = 'Walk_In' THEN 1 END)

also tilda, `, is used to differentiate columns whereas single ticks, ' , are used to differentiate strings. Did you type it in correctly?

@GrantSmith , in this context I don't think the else 0 matters.

Golfoholic

@GrantSmith I attempted include the ELSE 0 END, and it did return values that were able to be displayed on the card, but it just returned 0% for everything.

@jaeW_at_Onyx I went through and I am fairly certain I have typed it correctly.

I have written beast mode calculation previously for the overall conversion rate for the company. That can be seen below. unit_rented_c is obviously the column for those inquiries that did rent, and 'id' is a column with unique id's attached to all inquiries. Now I am trying to calculate a column for individual channels from the lead_source column and things just aren't going as smoothly.

sum(case when `unit_rented_c` = 'true' then 1 end)/count(`id`)

Golfoholic

@jaeW_at_Onyx I tried both the single ticks and tilda around the columns, and the result was the same.

jaeW_at_Onyx

@user048760 could you update your profile handle so it's easier to AT mention you?

can you give us a sample of your data. it sounds like it should be pretty straight forward.

split numerator and denominator into separate beast modes and pop it into a table card. if your denominator is zero then you know hwat the problem is (can't divide by zero) if the numerator is zero... it explains the zero. but hone in on the problem.

Golfoholic

@jaeW_at_Onyx SMH, I figured it out, it was a dumb oversite on my end. I appreciate your help. You helped point me in the right direction to discover this tedious mistake.

jaeW_at_Onyx

@user048760

glad you got there in the end!

can you pls mark this ticket as closed / answered closed?

Golfoholic

This is the first time I have posted a question and accepted an answer. Can you direct me on how exactly to close this out? I just keep clicking around and I am getting nowhere.

MarkSnodgrass

You likely can't accept an answer because it was created as a topic instead of a question. I did the same thing earlier because the orange button was initially default to create a topic.

@Anna Yardley Can you assist and get this post changed to a question so the person can mark one of these comments as an answer?

Golfoholic

@MarkSnodgrass Lesson Learned! Thank you.