Rolling Months on Pivots and KPIs

Lawrence Kilduff · 04/14/21

I have a question on how to update my KPI's where the data populates by month and will automatically start copying from the new month while dropping out an old month that is 13 months back? This will always show me the latest 12 months. I have multiple sites that I am running the reports from and would like to automate this to remove the hands on work on a monthly basis.

Any advice would be helpful. We are using the pivot app to pull and sort the data, but having the issue with mapping the new month. The above dashboard you see is only one of about 100 that are being tracked.

Thank you

Paul Newcome · 04/14/21

Can you provide a screenshot of how your data is laid out that you are referencing for your charts?

Lawrence Kilduff · 04/14/21

Thanks Paul. They are sheets sometimes like this when you need to edit the metric back to capture that rolling 12 months.

Paul Newcome · 04/14/21

Unfortunately your setup is going to require manual input each month because of the column names being the "Dates".

I am unsure of exactly how many different data points you are collecting and what formulas you are using to collect that data, but if you were able to "flip" the layout so that your "Dates" were going down a column, you would be able to automate it using a TODAY function.

Lawrence Kilduff · 04/14/21

Paul

thank you. I will try that with the team tomorrow and see if that will work. Greatly appreciated. Will let you know how it works out.

larry

Paul Newcome · 04/14/21

Happy to help.️

Feel free if you would like some pointers on how to automate the rolling portion. I have built out a handful of different "rolling" metrics for dashboards, so I should be able to at least point you in the right direction for those formulas.

Diane Moore · 09/24/21

@Paul Newcome, I just began using the pivot app and I watched a few videos. I am currently reluctant to use it too much because it is my understanding that when we roll into a new month, I must go in and rearrange, rename the new column. I am interested in what you shared above. If I swap the data round, does that prevent this ongoing maintenance issue?

TIA!

Diane

Paul Newcome · 09/27/21

@Diane MooreThat is correct. If you have the dates going down a single column and then separate columns for each data point, you should be able to use a formula to essentially automate the rolling time period with each month being on its own row and use formulas with cross sheet references that incorporate those dates to automatically display the correct amounts.

CE12345 · 07/26/22

@Paul NewcomeHi, would you be willing to share or send me one of your Smartsheet templates with the rolling average formulas? I am struggling with setting this up. Thank you.

Paul Newcome · 07/26/22

@CE12345I do not have a template that I can send.

Basically you would use a date type column with a formula to output the first of each month based on today's date. There are a few examples of formulas out here in the Community that should help you figure out those formulas where you need to subtract a certain number of months from a date. The "date" would be TODAY(). From there you would use your metrics formulas such as COUNTIFS or SUMIFS to get your totals and include a date range/criteria set to compare the month and year.

Rolling Months on Pivots and KPIs

Answers

Categories