# 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

- 7.7K All Categories
- 7 Connect
- 922 Connectors
- 245 Workbench
- 477 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 57 Visualize
- 205 Beast Mode
- 2.1K Charting
- 8 Variables
- 4 Automate
- 349 APIs & Domo Developer
- 84 Apps
- Workflows
- 15 Predict
- 3 Jupyter Workspaces
- 12 R & Python Tiles
- 242 Distribute
- 60 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 42 Product Ideas
- 1.1K Ideas Exchange
- 2 Community Forums
- 15 Getting Started
- 2 Community Member Introductions
- 50 Community News
- 18 Event Recordings
- 576 日本支部