12 month rolling pivot table with filtering capabilities
Hi all, I have a customer that is looking for a way to create a pivot table where value for each month is a sum of current month + previous 11 months (so in total we have 12 rolling months). For example, value for Dec'22 = sum of values from Jan'22 to Dec'22.
Below is the formula that they created for this but when there are gaps they are finding that the formula won't work any longer. Does anyone have any suggestions of how to resolve this filtering dilema?
For columns we have date (Month+Year), for rows we have some other dimension (in this example it's competitor). For rows we show top 10 items, while remaining items should be aggregated to "other" category.We want to be able to filter the table by any of dimensions that's available in the data (there's a lot of dimensions).Note on the data: data is very granular, there are multiple dimensions and date fields are daily (day-month-year).So far we've got a card which kind works with use of window functions, but the issues with this setup is that 1) calculation doesn't work if there is gap for even one month (there are many gaps, especially when you add multiple filters for other dimensions) 2) we're not able to show top 10 + aggregate rest to other category.Let me know if you have any questions, I can walk you through all details.Dataset used: Sales Opportunity LineFormula used:sum(`ACV Usd`)
+
lead(sum(`ACV Usd`), 1) over (partition by `LOB` order by `Estimated Close Year Month` desc)
+
lead(sum(`ACV Usd`), 2) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 3) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 4) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 5) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 6) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 7) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 8) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 9) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 10) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 11) over (partition by `LOB` order by `Estimated Close Year Month` desc )
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive