Beast Mode: Table Card Total Row Issues

swagner

I have a beast mode I'm trying to use that calculates individual performance goals. Using a table card, and including total and subtotal rows.

The first version of the beast mode was: (when sales dollars grow $2500 or more a 1% bonus is paid)

case 

when (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))>2500

then (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))*.01 

  else 0 

end

This give the correct result at the row level on the card but not for the subtotals and the total row. The beast mode is evaluating those subtotal and total rows on their own against the 2500 parameter instead of aggregating the individual values. So I changed the beast mode by wrapping the whole thing in a SUM() as follows:

SUM(case 

when (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))>2500

then (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))*.01 

  else 0 

end)

This produces the "An issue has occurred during processing. We are unable to complete the request at this time." message and the card will not render. Assuming that because the beast mode has SUM() at multiple layers its creating a conflict. So I removed the SUM() elements from within the case statements as follows:

SUM(case 

when (case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end-case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end)>2500

then (case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end-case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end)*.01 

  else 0 

end)

Problem is now that the results are not correct at the row level on the card, but the totals and subtotals are aggregating correctly (based on the incorrect rows).

What am I missing? (Forest for the trees)

GrantSmith

Hey @swagner

I’m on mobile so this is briefer than normal but because you’re attempting to aggregate on two separate levels which Domo doesn’t necessarily like. You might be able to calculate the sub totals or totals using a window function however I’d recommend using a data view on this data set to calculate the data on a daily level and then do the sub totals or totals on the card.

MarkSnodgrass

@swagner Some sample data might help with troubleshooting what is going on here, but I also wonder if considering a different approach might be easier. Have you considered using Magic ETL to get the totals you need? You could use a Group By tile to group by salesperson and year, then use the Rank & Window Lag function to put the previous year's sales total next to the current year's sales totals. You can then use the calculator or formula tile to easily calculate the bonus.

Would that work for you?

swagner

Sales Sample Data.zip

Sample data attached. I am trying to do with beast mode since the actual application is a "one off" analysis and the dataset we're using it 30M plus rows. Hate to create another aggregated dataset for just one card.

GrantSmith

Hey @swagner

I’m on mobile so this is briefer than normal but because you’re attempting to aggregate on two separate levels which Domo doesn’t necessarily like. You might be able to calculate the sub totals or totals using a window function however I’d recommend using a data view on this data set to calculate the data on a daily level and then do the sub totals or totals on the card.

swagner

@GrantSmith I started doing some testing with a window function but it's causing other issues. I agree with what you and @MarkSnodgrass are suggesting here - it's a data side thing I have to do. Thanks for your help. Hope you are doing well.

jaeW_at_Onyx

@swagner , you can't finish your requirement just using window functions in analyzer.

@GrantSmith is hinting you in the right direction. You're trying to functinally filter on an aggregated value (if the sum of activity in 2019 and 2018 is greater than x then .... )

If you've got 30M rows you can still get away with building the aggregation in Magic (SUM(AMOUNT) GROUP BY Year and Salesrep ) and JOIN to the raw data.

That way you can decide in analyzer whether to include that individual row.

OR do it in a DataSet View (more performant and you don't have to wait for ETL).

This is the extensive explanation, but if you fast forward to where we're playing with DSVs, you'll get a feel for how this can be accomplished. Just don't use a Window function, just do a normal GROUP BY and JOIN back to your data.

https://www.youtube.com/watch?v=eifSYZIcPzg&t=511s