A lot of projects have come up lately of needing a Rank & Window style function but needing it to be dynamic, mainly with Dates. I've gotten so close so many times, but the beast mode always fails on summing up the final amount, and not having the card aggregated by the rank that's assigned.
For example, I have a Beast Mode built, Date Rank:
DENSE_RANK() OVER (ORDER BY MAX(Week End Date) DESC)
I then have another beast mode that works with it in order to get the latest Retail sum for the latest week, that also works when the date is being filtered (which is why this wont work in an ETL):
CASE WHEN Date Rank = 1
THEN SUM(Retail $)
ELSE 0
END
This kind of works, but it returns all of the Rank values in the table card even though they aren't there. So there's a lot of extra rows with all 0s.
I've tried similar approaches with a different beast mode that doesn't utilize R&W:
SUM(CASE WHEN Week End Date = (MAX(Week End Date) FIXED ())
THEN Retail $ ELSE 0 END)
but this results in the following error:
An invalid column was specified in your request (the column does not exist on the DataSet).
which is ridiculous, since those columns do in fact exist. I'm not looking for a solution, since I know once doesn't exist. I can't use an ETL since this has to be dynamic with the dashboard date filters. I can't have a "Max Week End Date" column built to compare to, since that value is filtered out with certain date filters on the dashboard. Nothing will work, so this functionality just needs to be added to Domo.
