Help with Beast Mode

user17774

Hi there,

 

I'm a newby with Beast Modes and could use some help please! I created this Beast Mode which tells me the reject % for January. It works fine but I would like to modify it so it gives me the YTD average. Any help would be really appreciated. THX!!!

 

CASE WHEN ifnull(SUM (CASE WHEN `Month` = '1' AND `Event Name` = 'ACQUISITION' AND `Panel Serial Number` IS NOT NULL THEN 1 ELSE 0 END), 0) = 0 then 0

ELSE

(SUM(CASE WHEN `Month` = '1' AND `Event Name` IN ('IMAGE REJECT') THEN 1 ELSE 0 END))

/

SUM (CASE WHEN `Month` = '1' AND `Event Name` = 'ACQUISITION' AND `Panel Serial Number` IS NOT NULL THEN 1 ELSE 0 END) *100

END

jaeW_at_Onyx

Working with Beast Modes is most similar to creating columns and calculated fields on a table or pivot table respectively with the extra challenge that you cannot use 'cell references' to refer to other rows of data (i.e. previous row) without 'special code'.

 

Beast Modes / SQL are dissimilar to VBA insofar as you can't really declare variables, functions, or pass values by reference, or iterate using FOR LOOPS.

 

a beast mode is ONE column in a pivot / table or one complete expression in a SELECT clause which you can define pre or post application of an aggregate function like SUM / MIN MAX.  (i.e. you could write a CASE statement to recreate the IF() chains you're accustomed to in Excel and apply logic to a row if a criteria is met, or apply logic to a set of rows that have been aggregated using a GROUP BY clause).

 

your question how would i SELECT DISTINCT Strings.  well... i doubt that you want to reduce your data from n-thousand rows down to just a set of unique values, so much as GROUP n-thousand rows by a specific column (put it on the row axis, like a pivot table) and then sum(Amount) or do additional fancy math.

 

I guess to wrap it in a bow. While SQL might seem 'complicated' like scripting in VB or Python or any other scripting language, I do think data manipulation in SQL (and therefore Analyzer) is conceptually more analogous to how you approach handling data in Excel tables and pivot tables.

 

jaeW_at_Onyx

Ooof... this is not a 'beast mode question.' this is a SQL question.  and you can literally google "how can i calculate YTD in SQL' and get the answer.

I'm not saying that to be snarky.  I'm pointing out that Domo is built on technology that is well documented online and you'll find hundreds of resources that will give you detailed replies that will help you grow and build more flexible solutions (in this case) in SQL.

 

For example, your IFNULL is superfluous because your CASE statement has an ELSE clause.  Therefore it should never return a NULL.

 

I point this out, because simplification will make it easier to troubleshoot your code.

 

Similarly, the use of IN in `Event Name` IN ('IMAGE REJECT') is unnecessary and potentially less performant than =

 

... YOUR QUESTION ...

How to calculate YTD.  Instead of filtering on Month = 1 you want to filter on the months between 1 and Month of Today.

Or in other words `Month` <= 1 AND `Month` >= MONTH(Current_Date())

 

That said, be careful.  If you only filter on the Month, you'll include all years not just the current year.
You can extend your Criteria to include Year = Year(Current_Date()), But I would recommend extending your Date columns to include a YearMonth column, where YearMonth = Year * 100 + Month, or 202004.

 

Hope that helps!

user17774

Thanks for your help, I really appreciate it! You made me realize that I was approaching this in the wrong way and I know you weren’t being “snarky” ?!

I totally get what you’re saying about SQL but I’m finding it hard understanding how to integrate it into a Beast Mode. E.g. According to the Beast Mode Functions Reference Guide, DISTINCT is only available with the math functions COUNT and SUM so how would you use a Beast Mode to select a DISTINCT string other than using a filter? 

Although, I don't consider myself a programmer I can easily do what I need to with VBA but Beast Modes seem to be eluding me!

Any advice on how to best learn Beast Modes and SQL would be really appreciated.

Again, thx for your help and have a great weekend!

user17774

Just to clarify, I'm not looking for advice on how to learn SQL but how to integrate it into a Beast Mode.?

 

jaeW_at_Onyx

Working with Beast Modes is most similar to creating columns and calculated fields on a table or pivot table respectively with the extra challenge that you cannot use 'cell references' to refer to other rows of data (i.e. previous row) without 'special code'.

 

Beast Modes / SQL are dissimilar to VBA insofar as you can't really declare variables, functions, or pass values by reference, or iterate using FOR LOOPS.

 

a beast mode is ONE column in a pivot / table or one complete expression in a SELECT clause which you can define pre or post application of an aggregate function like SUM / MIN MAX.  (i.e. you could write a CASE statement to recreate the IF() chains you're accustomed to in Excel and apply logic to a row if a criteria is met, or apply logic to a set of rows that have been aggregated using a GROUP BY clause).

 

your question how would i SELECT DISTINCT Strings.  well... i doubt that you want to reduce your data from n-thousand rows down to just a set of unique values, so much as GROUP n-thousand rows by a specific column (put it on the row axis, like a pivot table) and then sum(Amount) or do additional fancy math.

 

I guess to wrap it in a bow. While SQL might seem 'complicated' like scripting in VB or Python or any other scripting language, I do think data manipulation in SQL (and therefore Analyzer) is conceptually more analogous to how you approach handling data in Excel tables and pivot tables.

 

user17774

Thanks so much for your detailed explanation, it really helped!