Best Of

@jkami talk to your CSM about the Variables beta, that would be the easiest path forward for this problem and if you have Partitions in ETL then it seems like that shouldn't be a big ask.

If your data goes back super far, you can filter your data using Dataset Views before pulling them in ETL, so if you know you only need 180 days of data that might be something you pre-flilter in a DSV before throwing it into ETL.

BUUUUT for your use case (rolling x days) the variables beta is probably the better tool for the job. but again, if the max i need to go back is X days, i'd probably build a DSV that prefilters the data, maybe drops a few unnecessary columns, then have support "optimize the DSV" and everything should feel much snappier.

@jkami you would want to take a look at the Variables beta (or is it GA? who knows??)

The Variables feature would allow you to pass a value "num_rolling_days" into your beast mode.

Case 

when `Invoice Date` >= SUBDATE(CURRENT_DATE(),interval num_rolling_days day) then 'Rolling Days'

End

until that time comes, you do have to do a ton of data gymnastics to give users a toggle between 15,30, and 60 days.

You say you can't use your View in ETL due to 'monstrous size'. how big are we talking here? If you design your pipeline to take advantage of Magic 2.0 + Partitioning (which i believe is in GA) you might be able to have a dataset with a monstrous size that you can still update.

@Jones01 i know i'm coming late to the party, but the long and short of it is, you can't use any date_part functions (year, month week) etc with a fiscal calendar, because as you've figured out, there is no way for the YEAR() function to know the span of your year.

if you wanted to be able to do FY_Year and FY_Month comparisons, you'd need a set of columns

current_date

current_fy_month

current_fy_year

(which could just be SELECT * from FY_Dimension where db_date = today())

then just CROSS JOIN that onto your transaction tables using a VIEW.

then in beast modes you can do calcs like

case when trans_fy_month = current_fy_month

which avoids using any of Domo's date_part functions.

@gbrown i like @RobSomers approach, but might extend it.

it sounds like you work on each item multiple times in a day, year, month.

so i might create a counter that tracks "this is the n'th time" somoene worked on this part. ideally i'd have an order_id, but in the absence of that i'd just ask if there's a new person working on this part. (is_same_owner = case when owner <> lag_1_owner then 1 else 0

then if i take the cumulative sum of is_same_owner i know "n_th_iteration_of_product'

then "is_completed = case when current_status=completed then 1 else 0 end"

now to know if the is_ticket_completed i can take max(is_completed) over (partition by n_th_iteration_of_product)

I can exclude all rows where is_ticket_completed = 1

this approach might give me insight into "how many times did we complete this item this month" "what is the average time to complete an item." and other stats. Just by running it through a ton of window() functions -- do it in Magic of course! :P

@Jones01 That looks like a spark line chart.

https://domohelp.domo.com/hc/en-us/articles/360043429353-Spark-Line-Chart

It's been around for a while but can be easy to overlook. Glad I could help. If you could accept the answer so others can hopefully find this easier in the future I'd appreciate it.