I looked on Discourse for an answer or work around regarding this, but it looks like the last time this was discussed on this board was back in 2015 - so I am hoping there are some new developments (or new ideas) to resolve the issue I am running into.
I am building a dashboard where it will display week-over-week daily average revenue for the past 2 completed weeks, segmented out by our accounts.
For the purpose of this dashboard, we want to rank the accounts based on the week-over-week delta only. To calculate this I am pivoting the "week" dimension from my "date" dimension group, then using a Table Calculation to calculate % variance and the actual delta between each week.
Here is where the problem lies, the comprehensive list of our accounts is over 10,000 rows. Because row limit in Looker is 5,000 I am unable to sort on the calculated field. This is the general layout of the report that the team would like to see:
In the dashboard Visualization, it will display only the "Top 20 Losers" (negative delta) and "Top 20 Winners" (positive delta).
I looked for any filtered measures to calculate week over week for my revenue measure, but I wasn't able to find anything that could give me this calculation via the LookML.
One final curve ball, this dashboard release will include a weekly PDF export (which is easy to set up), but the team also requested a Look where they can view and select a time frame (default is 2 completed weeks, but an analyst may want to see previous 4 weeks, or 5 weeks, etc). Again, I don't think the second ask is difficult with the way I set up my data, but the inability to sort on the calculated field makes that Look pointless if we have over 10,000 accounts (rows).
We recognize that we can sort on the "Revenue" field to sort revenue in descending order, but that won't give us the "Top Losers" and "Top Gainers" week-over-week - which is exactly what we need to monitor our accounts at a high level.
Are there any work around for this? Any feedback or ideas are welcomed.