Beast Mode Formula for Previous Complete Weeks

RocketMichael · 2022-09-01T14:17:13+00:00

There was an error rendering this rich post.

I need to display results in cards from the last complete weeks in a dataset.

(This dataset is on a 7-day lag. Thus making it not compatible with the Period over Period cards. Which is cray cray to me. There should be an option to align to the data set rather than today's actual date... I mean... lots to love about Domo. But some basic visualization platforms can do this... annnnnnyway)

Here are the date range groups I will need:

Note: Last complete weeks are Sun-Sat

Last Complete Week
-1 Week from Last Complete Week Total
Prior 3-Week Rolling Average from Last Complete Week
Prior 6-Week Rolling Average from Last Complete Week
Prior 12-Week Rolling Average from Last Complete Week

Ex: "Prior 3-Week Rolling Average from Last Complete Week" means -1,-2,-3 weeks back from the Last Complete Week

Each of those totals will then be compared to the Last Complete Week to show the rate of change like the example below.

Question:

What Beast Mode formula can be used to filter data to the Last Complete Week and the subsequent data range groups I listed above?

Find more posts tagged with

Date

Beast Mode

Comments

MarkSnodgrass

@RocketMichael to get the date of the last complete week, you can use this formula:

DATE_ADD(`dt`, INTERVAL (7- DAYOFWEEK(`dt`)) DAY)

to get the rolling averages, I will refer you to this post that goes over it.

https://dojo.domo.com/main/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest

You would inject your last complete week formula into this.

Hope this helps.

RocketMichael

@MarkSnodgrass Thanks for the link!

So, would the 3-week rolling average prior to the last complete week look like this?

(LAG(SUM(DATE_ADD(`dt`, INTERVAL (7- DAYOFWEEK(`dt`)) DAY)), 3) OVER (ORDER BY `dt`) 
+ LAG(SUM(DATE_ADD(`dt`, INTERVAL (7- DAYOFWEEK(`dt`)) DAY)), 2) OVER (ORDER BY `dt`) 
+ LAG(SUM(DATE_ADD(`dt`, INTERVAL (7- DAYOFWEEK(`dt`)) DAY)), 1) OVER (ORDER BY `dt`)) / 3

MarkSnodgrass

No, your sum would be your sales amount column, not the date calculation. What does your data look like? Do you have one entry per day? One entry per week? That will make a difference in the lag.

RocketMichael

@MarkSnodgrass Oops. Misunderstood there.

The data contains singular daily entries. It's always 7 days behind today's date in terms of data availability.

jaeW_at_Onyx

@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