The short version, I need the following query to work somewhere in DOMO ETL:
AVG(`Score`) OVER (PARTITION BY Company
ORDER BY 'Date` RANGE BETWEEN INTERVAL '90' DAY PRECEDING AND INTERVAL '0' DAY FOLLOWING)
More detail:
I tried using group by but cannot use window functions, formulas but can't use aggregations, and rank and window but is only limited to preceding rows (which would be fine if I fill in the dates for each company using the connector, but I'd like to keep it simple).
Is there a beta function to fully allow ETL window functions, or am I missing something? I can probably accomplish this in a beast mode (haven't tried), but this is only a small piece of many other connected ETLs. This one is no different and feeds into another ETL to produce a total score or transposes for various graphs. I'd rather not have to make multiple beast modes and redo the structure of this particular project just for one piece to work.
I could use Python pandas SQL but I would like to avoid using the scripting tiles if possible
PS - I have no idea why Company happened to code inline correctly compared to Date and Score. Pointing it out as an interesting curiosity in case that piques the interest of any DOMO folks.