Month over Month Variance w/ live data help.

aderderian

Hi SS Community,

I am trying to create Month over Month varience report for my project management work flow. I currently have a Smart Sheet and Sales Force integration that updates my data live 2 ways, and that's where my problem begins.

Below is a screenshot of Project Status dashboard graph as of 10/28/24 . I'd like to be able on 11/28/24 compare any changes. The below chart, as mentioned is live, so the information in my intake sheet is always in real time.

Any suggestions?

So, Ideally I'd like to be able to see how much change per stage happened month over month in a report that I can than turn into a graph.

Ex.

From October to November, we reduced our backlog in "Configuration Stage" by $300,000.

Thanks in advance!

jmyzk_cloudsmart_jp

Hi @aderderian

Here is a demo solution.

In the image below, the Project_Status_This_Month sheet gets live Data for Today, whereas the Project_Status_Last_Month sheet gets the Last Month's date on the same day as today.

https://app.smartsheet.com/b/publish?EQBCT=a2f6473558a046c6815917e348068d83

The Project_Status_This_Month sheet has the following helper columns and formula.

[This Month] =INDEX({12 months of the year : Month}, MONTH(Date@row))
[Last Month] =IF(MONTH(Date@row) = 1, "December", INDEX({12 months of the year : Month}, (MONTH(Date@row) - 1)))
[Last Month Total Baglog] =INDEX({Project_Status_Oct_28 Total Backlog}, MATCH(Project@row, {Project_Status_Oct_28 Project}, 0))
[Change] =[Last Month Total Baglog]@row - [Total Backlog]@row
[Description] ="From " + [Last Month]@row + " to " + [This Month]@row + ", we reduced our backlog in " + Project@row + " by $" + Change@row

Using the Project, Total Backlog, and Last Month Total Backlog columns' data, you can create a chart like the above dashboard.

Using the above This Month and Last Month formula, I used a sheet like this to get English words for 12 months of the year.

aderderian

Thank you for the response- My confusion is that the report that is generating the breakdown is a chart pulling from a Master Intake sheet- so I cannot add any helper columns. It's essentially read only.

If I create a new grid, How am I to automatically bring in the data so I can set these formulas up?

jmyzk_cloudsmart_jp

Hi @aderderian

To generate a Month-over-Month (MoM) variance report using a Master Intake sheet with read-only access, you can create a secondary sheet with unique IDs (e.g., "Row ID") that match the Master sheet. This approach allows you to cross-reference data and apply formulas without modifying the Master sheet. Here’s how you can do it:

1. Create a Secondary Sheet: In your new sheet, add a "Row ID" column that matches the unique IDs in your Master Intake sheet.
2. Use Cross-Sheet References: Use cross-sheet formulas to pull the necessary data from the Master sheet into your secondary sheet. For example, you could use INDEX and MATCH to reference columns like Project, Backlog, Status, etc., based on the unique Row ID. This ensures that the data in your sheet stays current with live updates from the Master.
3. Capture Monthly Data for Comparison: If the Master sheet includes a date-related column, such as a specific date or month associated with each row, you can bring in last month’s data dynamically. Use formulas like INDEX and MATCH to reference rows with dates from the previous month and compare them with the current month's data in your sheet. This enables automated, up-to-date month-over-month comparisons without manual snapshots.
4. Calculate MoM Variance: With "This Month" and "Last Month" columns, use formulas to calculate the difference. For example, a formula like [Change] = [This Month Backlog]@row - [Last Month Backlog]@row will track variance in backlog.
5. Summarize and Visualize: Once you’ve created MoM variance data, you can use Smartsheet's Report Builder or link this secondary sheet to a dashboard for visualizations, like month-over-month charts, that illustrate changes in project stages.

This method offers flexibility with read-only Master sheet data and allows you to generate meaningful comparisons in your own sheet.

aderderian

@jmyzk_cloudsmart_jp

My master sheet doesn't have dates associated with each row per say. It's live 2-way data pulling from SalesForce.

I was able to create a secondary sheet that pulls automatically from the connector the unique id's, But I dont think I am able to set this up dynamically unless there is something I am missing. This means I may have to take a "manual snapshot"…. I am unsure.

Would you be open to being more hands-on and help? If so, can we connect offline?

jmyzk_cloudsmart_jp

Hi @aderderian

Regarding your secondary sheet setup, you can indeed set up a dynamic way to handle IDs and data referencing without needing to take manual snapshots. Here’s a detailed approach using the MATCH function to create an ordered list of IDs or numbers and then using cross-sheet formulas like INDEX(MATCH) or JOIN(COLLECT) to utilize these IDs effectively:

1. Creating a Number List for Unique IDs: On your Master sheet, assume you have unique IDs pulled from Salesforce. You can create an auxiliary column on this Master sheet to create sequential IDs. Use the formula =MATCH([@ID], [ID]:[ID], 0) to generate a sequential number for each unique ID.

https://app.smartsheet.com/b/publish?EQBCT=b91ff1cfbc1f4ca4994b9f417bcd2169

1. Setting Up the Secondary Sheet: Pre-populate your secondary sheet with a sequential numbering column that starts with typical entries such as 1, 2, 3 and extends up to 99, 100, for example, to ensure it is large enough to accommodate all possible IDs from the Master sheet. This extended range ensures that you have a robust reference base to pull data dynamically for each unique ID without missing any entries.
2. Using Cross-Sheet Formulas:
   • INDEX(MATCH): This can fetch the corresponding data for each ID. For example, to fetch the Project name, use =INDEX({Master Project Range}, MATCH([Number]@row, {Master Number Range}, 0)).
   • JOIN(COLLECT): This can be useful if you need to concatenate or collect multiple entries based on specific criteria across sheets. This is also useful as you do not have to handle errors if there is no number ID in the master sheet corresponding to the second sheet's ID.

https://app.smartsheet.com/b/publish?EQBCT=d2ff727793224ce7af3d6d94de773be8

By organizing your IDs and their references this way, you can maintain a dynamic linkage between your Master intake sheet and the secondary sheet, ensuring that updates from Salesforce are reflected in real time without manual updates.