Best Of

@damen In your screenshot where you showed the "Current Year" beast mode, be sure to use the " ` " symbol around your Transaction Date and amount fields, instead of " ' ", so that Domo knows you are referring to columns.

It should look like this, when forcing to a number as @GrantSmith suggested:

case when YEAR(`Transaction Date`) = YEAR(CURDATE()) and MONTH(`Transaction Date`) = MONTH(CURDATE()) then `amount`*1 end

To give some context, I’ve seen MySQL dataflows take 4-5 hours and then their Magic 2.0 replacement take 10-15 minutes. Unless you’re doing like a stored procedure Magic 2.0 is the way to go.

This will run faster is you utilize indexing in MySQL (help article), which would require a transform for each of your tables to create two new columns for the month extracts you are joining on...e.g. LDGRDATE_Month and LDGRDATE_PriorMonth. Once those columns are created, you can add an index to them prior to joining them in your statement above, which should improve runtime.

I would also recommend utilizing Magic ETL 2 and using the formula tile to extract the month parts and then joining on those columns, this would likely be the most optimal runtime.

You'll want to do this utilizing an ETL. It's possible to do it in a Beast Mode with window functions however it would start on the first date that is filtered and not going back entirely in time to the start of your ledger. For example if you only displayed this year the beast mode variant would have $0 to start the year rather than the carry over from last year.

Here's a prior solution to using MySQL ETL to get a running total you could look at.

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Running-total-ETL-or-MySql/m-p/39593

@damen After adding the column that calculates the days between orders, you can do a Group By tile to group the customers together and average the days between orders.