Best Of

You can get around the fact that the qtyAvail is repeating by using the average function in your beast mode like this:

SUM(orderQty) / AVG(`qtyAvail`)

In your OVER clause, you should be able to add ORDER BY and list current run DESC, total current run DESC so that it will sort by current run first and then total current run after that.

@MarkSnodgrass and @RocketMichael the risk with LAG() is that if you have a gap in your data (i.e. sales on 6 out of 7 days) that would throw off LAG because LAG 7 would actually be the 8th day back.

instead you might consider trying to use Variables (beta feature maybe GA check with your CSM) to address the problem.

basically you define a variable report_date which you can then alter for all of your calcs

avg_rolling_14 = 
sum(case when trans_date >= date_add(report_date interval -14 days) and trans_date < report_date then amount end)
/
count(case when trans_date >= date_add(report_date interval -14 days) and trans_date < report_date then report_date end)

in this example report_date is user define-able and you're calculating total sales within 14 days divided by the number of days of sales within 14 days.

Only hitch in the plan is that Domo does not support Dates as variables so you have to use current_date - date_offset as a surrogate for report date.

in other words your variable is date_offset and replace all instances of "report_date" above with...

date_add(today(), interval - date_offset days)

this is pseudo code so test it, but it should work with a little tweaking :D

@RocketMichael

(
(CASE when `Period Type`='Current' then `Shipped CoGS`end) - 
(CASE when `Period Type`= 'Last Month' then `Shipped CoGS` end)
) / (CASE when `Period Type`='Last Month' then `Shipped CoGS`end) 

you are calculating your beast mode at the row level. in other words, take one row in isolation and then apply your beast mode to it.

you Period Type for one row cannot be both 'Current' AND 'Last Month' at the same time. therefore it is IMPOSSIBLE for one row to return a result.

you have to apply your beast mode to an aggregate (which is exactly what @RobSomers ) is demonstrating.

(
SUM(CASE when `Period Type`='Current' then `Shipped CoGS`end) -
SUM(CASE when `Period Type`= 'Last Month' then `Shipped CoGS` end)
) / SUM(CASE when `Period Type`='Last Month' then `Shipped CoGS`end) 

"across all rows take the sum of shipped cogs when period type is current."

"across all rows take the sum of shipped cogs when period type is last month" etc.

@RocketMichael Is your dataset organized at day grain like your table? Or are you doing the aggregation in your card? If it's in your card, you're going to need to throw SUM around all your case when statements:

(SUM(CASE when `Period Type`='Current' then `Shipped CoGS`end) - SUM(CASE when `Period Type`= 'Last Month' then `Shipped CoGS` end)) / SUM(CASE when `Period Type`='Last Month' then `Shipped CoGS`end)