Best Of

Then I'd go the second option where you'd need to create a dataset with all of the possible SKU / Date combinations.

A starting point as @MarkSnodgrass mentioned is the Date dataset from the Domo Dimensions connector, filter out any future dates to speed up processing, add a constant of 1 can call it 'Join Column'.

Get a unique list of your SKUs and add a constant of 1 can call it 'Join Column'. Join these two together on the Join Column.

Take that resulting dataset and left join it to your dataset (yours is on the right side in the join tile) and then use the rank and window tile to calculate the 30 day rolling sum, then use the formula tile or calcuation tile to divide that by 30 to get your average.

PS. @MarkSnodgrass I beat you :)

@GrantSmith , but @MarkSnodgrass solution is cleaner.

while buiding the universe of SKUs and Dates is an option, all that facilitates is building an (expensive) window function.

a date dimension with a lag_from_current_date (0, 1,2,3,4,5) would be a much cleaner way of reporting on 30 day rolling average.

even with an offset calendar (rolling average today, rolling average 7 days ago, rolling average 2 weeks ag) you'd have a clean JOIN with a Date Dim and no window function.

It might be easier to do a formula by itself and then label each case as Keep or Remove and then do a filter on that field.

So the formula tile would have something like "Filter Flag" and it could be like.

So basically you are listing all the cases you want removed and then everything else you keep.

Then in the next tile filter when "Filter Flag' is Keep.

@gospel That is correct. You could also do the opposite if that is easier for you if you reverse your signs like this:

CASE WHEN DATEDIFF(CURRENT_DATE(), `dt`) <= 7 THEN '1 week or less'
WHEN DATEDIFF(CURRENT_DATE(), `dt`) <= 14 THEN '2 Weeks'
WHEN DATEDIFF(CURRENT_DATE(), `dt`) <= 21 THEN '3 Weeks'
ELSE 'More than 3 weeks'
END

You can create as many WHEN statements as you want and then use the ELSE statement to do anything beyond that.

Yes, you could do this:

CASE WHEN DATEDIFF(CURRENT_DATE(), `dt`) >= 21 THEN '3 Weeks or more'
WHEN DATEDIFF(CURRENT_DATE(), `dt`) >= 14 THEN '2 Weeks or more'
....
END

You will want to work chronologically, though in order to make it work. In this case going from greatest to smallest since the case statements stop evaluating and exit out once they find a match.

@MarkSnodgrass given the vertical ticks, 'Marketing' , would be interpreted as the text, Marketing, even if there was a column called `Marketing`-- note the tildas, `, .