Graph 3 months running totals

khankoff

I have a worksheet that returns the count of requests for a given week, and I have set it up to work for a year.  What I envision is creating a graph that illustrates a running three months worth of requests at a time.  I would like the graph to automatically update so that the range always displays the last three months.  In other words, the graph would display 12 weeks of data, and shift one week to the right every week such that one week drops off, and another is added in the range.  Can this be done in Smartsheet?  Seems to me it's possible in Excel with a rolling chart using the Offset function.

Rohann@6STEP

You could use a report set to the 90 day timeframe and use the report data to build out the graph...?

Paul Newcome

It is not possible shifting horizontally, but it is possible by (sort of) shifting vertically.

 

Use a single date type column. Have your dates going down this one column. You can use formulas to automate what the actual dates are to encompass whatever range you want. In the next column is where you put your metrics referencing the date cells instead of hard coding dates in.

 

Set your graph up to use the first column as your data labels. As the date changes, so will the dates in the table you built which will in turn update the labels on the chart as well as the metrics data itself to reflect the desired range.

khankoff

Hi Paul,

I think you may be on to something, but I'm unclear on what you mean by using formulas to automate what the actual dates are.  Also trying to wrap my brain around putting in metrics referencing the date cells, and not hard coding dates.  I guess you mean I would point the metric to the cell, and not to the date, but again, I am going to have to think on this for a bit.  Any further guidance is appreciated.

khankoff

Thanks.  Looking into this as a possibility.

 

 

Paul Newcome

To display the last 3 months, you would basically use the very bottom row as today's date, and then as you go up the column, subtract a day.

khankoff

Thanks.  I'm doing this by week (not day), but I believe I understand the concept you've suggested, and am starting to play around with it.  

Paul Newcome

Duh. You said that already. Maybe I should pay better attention. Lol.

 

To get Monday of the current week, you simply need to enter "Mon" with out the quotes into a date type column. Let's just assume that's in row 12. In row 11 you would have 

 

=[Date Column]12 - 7

.

In row 10:

=[Date Column 11] - 7

.

So on and so forth. That would give you the Monday. "Fri" without the quotes gives you Friday.

.

Either that or 

 

=WEEKNUMBER(TODAY())

=WEEKNUMBER(TODAY()) - 1

=WEEKNUMBER(TODAY()) - 2

.

I personally am  not a fan of the WEEKNUMBER solution simply because things go haywire the first few months of a new year, and you would need to incorporate the YEAR function into everything to ensure you are pulling from weeknumber 27 of the current year as opposed to this year AND last year if you have data from multiple years within the same range.

.

I have a formula tucked away somewhere (if your interested) that will display the 1st business day of the first month in the range. The next row contains the following Monday of that month. From there it jumps Monday to Monday then will adjust the displayed dates to show the last business day of this month.

khankoff

Once i wrapped my brain around how to achieve this, I believe it's actually a pretty solid idea.  Thank you for sharing.

khankoff

Thanks, Paul.  I'm going to toy with the suggestion you've made above (Date column - 7), and see what it gives me.

Ken

Paul Newcome

Happy to help. I also just noticed that I fat fingered a little in my above example.

 

[Date Column 11] should actually be [Date Column]11.

 

It should look something like this...

-------------------------------------------------

Row #                  Date Column

   1                       =[Date Column]2 - 7

   2                       =[Date Column]3 - 7

   3                       =[Date Column]4 - 7

   4                       =[Date Column]5 - 7

   .                        .

   .                        .

   .                        .

   .                        .

   .                        .

  12                     Mon

-------------------------------------------------

You would then pull your metrics based off of [Date Column]@row. You can set [Date Column] as your headers in the chart and have the metrics displayed accordingly.

khankoff

Thanks again, Paul.  I'm going to play with this idea as well.

Paul Newcome

Happy to help!