Dashboard Chart to Show Rolling 12 Months

Options

I created a sheet that has a primary column for "product type" and additional columns with months listed for the next two years (Dec '20, Jan '21, Feb '21, etc.) which indicates the month in which those products will be implemented. I then have 3 rows for 3 different product types. This data is pulled from a Summary Sheet. It looks something like this:

Product Type Nov '20 Dec '20 Jan '21

Product Type 1 2 1 1

Product Type 2 2

Product Type 3 1 2 5

All Products 3 5 6


I used the following formula to count how many of each product type will be implemented each month for the next two years (with dates adjusted for each month):

Nov '20: =COUNTIFS({Summary Sheet Range 1}, "product type 1", {Summary Sheet Range 2}, >=DATE(2020, 11, 1), {Summary Sheet Range 2}, <=DATE(2020, 11, 30))

I created two columns to roll the data up to count how many (of all product types) will be implemented each month:


I then created two additional columns "Month" and "Implementations", which I used in the attached dashboard view.


Is it possible to create a view of the above chart that will show a rolling 12 months automatically?

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Kristen Thompson ,

    I'm sure there's a solution. My thought is to create a new sheet that feeds the chart. Make a dynamic table that always depicts the last 12 months using MONTH and YEAR functions. The table will have 13 columns (or however many months you want to graph +1) and a row for each product type. Use the month and year values for that column or row in your countifs to populate the table.

    Count if would be like:

    The Month formula for current month is =Month(today()). The other months are calculated as:

    The year for the current month is =Year(today()). Years are then calculated by confirming that the month didn't get bigger which would would mean you're in the previous year:

    Make sense?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    Hi @Mark Cronk I am currently trying to work on a 12 months rolling average to be presented on a dashboard too and I came across your answer above.


    I am trying to understand your formula above. However for the final part on the YEAR formula, I couldn't understand: =IF([Month-12]1 < [Month-11]1, [Month-11]@row, [Month-11]@row-1).

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Vivien Chong ,

    This IF statement changes the year to the previous year if the month drops instead of rising. The table bases everything off of today's month and year to create a rolling chart. So, if the month is 1 the previous month is 12 and the previous year is the current year -1. In Jan 2021 the previous month is Dec 2020. Make sense?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    Thanks @Mark Cronk let me try to put it on paper to see how it works.

  • brfried
    brfried ✭✭
    Options

    Thank you @Mark Cronk. This worked for me, with a few tweaks.

    Added a helper row to convert the month number to a month name.

    🔄.Reversed the Column order. This allowed me to chart the current month right justified.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I personally prefer a vertical structure. Dates in a single column, then use separate columns for each of the various products. Then you can use a cell reference in place of a hardcoded DATE function which would allow you to dragfill formulas instead of having to create 24 different formulas.



  • Lauren Dominique
    Lauren Dominique ✭✭✭✭✭✭
    Options

    @Paul Newcome Does your proposed vertical solution allow for a dashboard chart to automatically update each month without having to update the source data?

    If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!