Beastmode across seperate rows, is it possible?

user046467

Not sure if this is going to work at all or that I made a mistake.

 

I've added 2 screenshots.

One with the actual beastmode script and a screenshot from the table.

Is it possible to exectue this script? I get a valid response but it doesn't return any values. Most likely because it's a script across multiple rows.

 

Hopefully some can help me out or just straight up tell me that it won't work at all

GrantSmith

Hi @user046467 

 

This is because your CASE statements have no ELSE clause causing them to return NULL. When SUM comes across NULL it treats the entire value as NULL. To correct this you'd need to make sure your CASE statements return a value. In this case since you're adding values together with SUM you can add 'ELSE 0' to each of your case statements to fix your beast mode.

jaeW_at_Onyx

While @GrantSmith 's assessment that you lack an ELSE clause is not untrue, I don't believe it'll get you to the heart of the problem.

 

SHORT ANSWER.  Restructure your data or you'll probably get the wrong result.  

 

LONG ANSWER

Yes ... you could aggregate the data and get a result.

 

SUM(CASE WHEN 'ConversionTypeName 2` = 'Sale 1' then ConversionValue end)  /// yields 98.

*

SUM(COST) // yeilds 72.17

// yeilds 7072.66

 

Which I assume is 'wrong'.

 

What you probably want to do is get the conversion rate into the same row as Cost using a JOIN somehow.  Alternatively, you just want ONE conversion rate for type Sale 1. and then the SQL i wrote earlier will work.

SUM(CASE WHEN 'ConversionTypeName 2` = 'Sale 1' then ConversionValue end)  /// yields 48.

*

SUM(COST) // yeilds 72.17

 

 

But then you cannot have AccountDescriptiveName or ConversionType on your axis.  Because NO you cannot aggregate across rows. **

 

** technically you can aggregate across rows, but given the SQL you're writing and the use case you're describing, I don't believe window functions are appropriate.

 

Hope that helps.

user046467

Thanks you both for your response.

I ended up restructuring my data and making the calculation much simpler. By using join and adding a constant to the conversions tab. That seems to work ?