Beast Mode is valid but does not print all options, how can I fix it?

EMart

Created the beastmode below and although its valid I'm only seeing options "Low Performers" and "Other". The beastmode is suppose to look at revenue and at month-12 revenue and meet certain qualifications to identify it as either Non Public Videos, Low Performers or Videos Exceeding Lifespan. Thank you in advance!

i.e. if total revenue >= 1000 and Month-12 revenue < 100 then Videos Exceeding Lifespan.

CASE

WHEN SUM((CASE WHEN (DATEDIFF(date,published_date) >329 AND DATEDIFF(date,published_date) <=359) THEN '12 Month' END)) <= 0 THEN 'Non Public Videos'

WHEN SUM(revenue)< 1000
AND SUM((CASE WHEN (DATEDIFF(date,published_date) >329 AND DATEDIFF(date,published_date) <=359) THEN '12 Month' END)) < 100 THEN 'Low Performers'

WHEN SUM(revenue)>= 1000
AND SUM((CASE WHEN (DATEDIFF(date,published_date) >329 AND DATEDIFF(date,published_date) <=359) THEN '12 Month' END)) < 100 THEN 'Videos Exceeding Lifespan'

Else 'Other'
END

MarkSnodgrass

The way it is written, you are trying to SUM the string '12 month'. That won't work very well. With these types of case statements that are giving me a problem, I try to break it into smaller chunks and make sure that it is returning a value that I expect, and then paste that statement into the larger one.

Try making a beast mode of this section:

SUM((CASE WHEN (DATEDIFF(date,published_date) >329 AND DATEDIFF(date,published_date) <=359) THEN '12 Month' END))

and see what values it returns and then make the necessary changes to this one and then add it back to your larger statement.

MarkSnodgrass

The way it is written, you are trying to SUM the string '12 month'. That won't work very well. With these types of case statements that are giving me a problem, I try to break it into smaller chunks and make sure that it is returning a value that I expect, and then paste that statement into the larger one.

Try making a beast mode of this section:

SUM((CASE WHEN (DATEDIFF(date,published_date) >329 AND DATEDIFF(date,published_date) <=359) THEN '12 Month' END))

and see what values it returns and then make the necessary changes to this one and then add it back to your larger statement.