Trailing 12 weeks/months style report

AFlint

I'm looking to create sheet that will roll up data for a trailing time period. I can collect the data easily enough, but of course, the data changes over time. Is there a way to collect the data, then freeze it and collect new data in a new cell? I don't have a problem pre-building the sheet with all of the future dates, but I don't know how to freeze the collected data.

Example:

On Friday, Oct 1, outstanding revenue is $100,000. On Friday, Oct 8, it is $87,000

I would want my report to have 2 columns, one for Oct 1 and the Other for Oct 8 with those amounts in a row labeled "Outstanding Revenue".

I currently do not have access to Pivot, Data Mesh, Control Center, or 10,000 ft, but please feel free to indicate of one of these modules is necessary for this type of functionality.

Thank you

Paul Newcome

You would need 3 sheets in total. The first will run the base metrics. You would set up a copy row automation to occur on a regular basis that takes these values and copies them over to the second sheet. Insert a new Created (date) column on this second sheet. The third sheet would need to be "pre-built" so that you have the columns already created and from there use an INDEX/COLLECT pointing at the second sheet to pull the appropriate value from the data column based on the label column and date column lining up.

Paul Newcome

You would need 3 sheets in total. The first will run the base metrics. You would set up a copy row automation to occur on a regular basis that takes these values and copies them over to the second sheet. Insert a new Created (date) column on this second sheet. The third sheet would need to be "pre-built" so that you have the columns already created and from there use an INDEX/COLLECT pointing at the second sheet to pull the appropriate value from the data column based on the label column and date column lining up.

AFlint

@Paul Newcome

Thanks for the response. I'm walking myself through the process, and I just want to make sure I understand.

In your example, sheet 1 and sheet 3 make sense- I already pictured that. Sheet 2 is the part I was missing. As I think about it I can see the logic.

Regarding the copy automation, you're saying just copy the roll-up row that has the metric I want to freeze, right? The automation would just add it to the bottom of sheet 2, then apply a created on date, and I can use the created on date to age it into the trend. Am I picturing that correctly?

Paul Newcome

That is exactly it.

AFlint

Ok, discovered a glitch, and want anyone seeing this thread later to be aware:

If you try to copy a roll-up row (Parent) via automation, it will take the Child row(s) with it. In order to only take the data from the parent, you actually need a 4th sheet.

Sheet 1: Source data

Sheet 2: Mirror of Sheet 1 using a vlookup to put the top-line (Parent) values into a row on Sheet 2. This is the sheet where you set up the automation to copy the rows on a cadence to Sheet 3

Sheet 3: This is the receiving sheet that applies a date stamp to "freeze" the data you want to trend. Using an auto-numbering column set for Date Created.

Sheet 4: Depending on the structure of your data, may be optional. If your data on Sheet 3 is chart- or report-friendly, you could build your metrics from that sheet. Otherwise, this sheet would be the tool used to structure and report your data.

I'm sure with a little creativity and experimentation, I can get Sheet 2 to transpose the data points into a chart- or report- friendly format, thereby copying to Sheet 3 pre-formatted and eliminating the need for Sheet 4.

Hope that makes sense.

Paul Newcome

@AFlint If you include some kind of helper column with an indicator as to whether or not it is a parent row, you should be able to just go ahead and have the copy row automation grab the parent and child rows, then use this indicator in the final sheet to further filter your metrics formulas.

AFlint

I actually figured out a way to do it on the front end. I set the automation to run based on the condition in the helper row (a check box) and this allowed me to copy only the parent.

Thanks again.

Paul Newcome

@AFlint Great. I had thought about that as a possible solution but didn't have a chance to test it yet. I didn't want to throw it out there only to have it not work as expected.