Need Help Automating Copy of Historical Values

Joe Kowan

Hello,

I have a sheet that shows Annual Sales Forecast data in two columns - one column for current month forecast, one column for previous month forecast. I use those columns to calculate a third column to show variance from previous to current month. The current month column is pulling values from a separate sheet that has a data shuttle flow to bring in sales forecasting data on a monthly basis. Each month I have to manually copy the values from the current month forecast column into the previous month forecast column, and then run the data shuttle to get the latest figures in the current month column.

I'm looking for an automated way to move the values from current month forecast column to previous month forecast column, simultaneously with the data shuttle workflow in order to make sure I always have the historical numbers saved before overwriting with the new month figures.

Appreciate any ideas to solve this!

Thanks

Samuel Mueller

Instead of overwriting the data have you thought of just continually adding data and then use a report to filter to the current month?

Joe Kowan

Thanks for that! It's a good idea, I'm just not sure how I would implement.

The monthly file we upload to Smartsheet contains current year forecast metrics by Client (rows) & Fulfillment Partner (columns). The forecast metrics change every month, but the structure stays the same. So the Data Shuttle automation is matching on the client name and updating the metrics by partner.

Is there a way you're aware of that I could continuously add the data and just filter to current month, while being able to report on variance by month?

Samuel Mueller

@Joe Kowan if you continuously add data each month, you could use a separate sheet to do the calculations based on current month versus previous month, and as the source data gets update your formulas would auto update as well. to get data shuttle to add data though you need a unique identifier. There are some workarounds you could do with datamesh to make that happen if you have datamesh. Or if you have bridge you could create a workflow that moves that data from one column to the other and update the current month with your new data shuttle.

Joe Kowan

Thanks @Samuel Mueller. We do not have Datamesh or Bridge. Are you recommending create a new sheet every month? Not sure I'm following.

Samuel Mueller

No, so essentially you would have one sheet (the source sheet) that gets updated by data shuttle that has then entire fiscal years worth of data, each month that gets updated with the new data in addition. Then you have a separate sheet that is formula based, referencing the source sheet. In this sheet you would have formulas that look at the current month in one column, and the previous month in a second column, and then do any analysis you need in the third column, etc.. Since it's formula driven, as data gets updated in the source sheet, your formula sheet will automatically update.

Joe Kowan

Is there an automated way to update those columns? (Current month and Previous month)

Samuel Mueller

If you are able to get the whole years worth of data in your data sheet, and update the formulas for current month and previous month on your annual sales forecast sheet then it would update automatically. Without changing the source data and data shuttle, then the only way you could achieve this would be through the API, or Bridge.

If you change your data source to have all fiscal year sales data, the formulas in your Annual Sales Forecast sheet would be something like

=sumifs(sales, account, account@row, month, month(today()), year, year(today())) for the current month and

=sumifs(sales, account, account@row, month, month(today()) - 1 , year, year(today())) for the previous month

this would update your data automatically as it's updated from datashuttle.

Does that make sense?

Alex Argumedo

This is a late comment, but I feel it is valid. My take on this matter is that Smartsheet is not intended to do any historical action, if you need history get a different system or a database to store it, Smartsheet is best at current data or maybe a few months, but no historical, you will hit all kind of limits soon enough and all the planning will go to waste or it won't be scalable.

I'm not trying to be negative, SS is good at what it does, but historical is not one of those things.