automate charting for next year

OshaK

Hello,

I have a workflow where I track certain statistics per group and per individual.

I created the dashboards for multiple fiscal years and charted the sheet that has statistics manually for each metric.

Is there any way to automate or otherwise reduce manual next year charting without manually recharging each chart for the next year? It's a big deal for me because total for everyone I would have to recreate around 150 dashboards with 8-12 different charts on each of them every year.

1. the reference sheet:

2. the dashboard example with the charts:

3. How I created the charts. Each chart has a month column and the metrics. The rows are selected based on the year and I chart only 12 rows per each chart.

4. This is the structure of my workflow - each individual or division has a set of years with 3 dashboards (with multiple charts) per every year.

Thank you so much!

Paul Newcome

I would create two helper columns. One that is text/number called "Year" and the other is a checkbox called "Current Year".

In the [Year] column, use this column formula:

=VALUE(RIGHT(Months@row, 4))

Then in the [Current Year] formula you would use this column formula:

=IF(Year@row = YEAR(TODAY()), 1)

Finally you would create a report to show the columns you want filtered to only show rows where [Current Year] is checked and use this report to build your chart.

OshaK

Thank you so much, @Paul Newcome . I already have a Year column with the formula :

=YEAR([Date of Event]@row)

I can create a new column for the "Current year" - what will happen with the previous years? I need to keep the stats for older years too.

Also, our fiscal year doesn't match the calendar year so I need to chart not Jan-Dec YYYY, but Sep YYY1-Aug YYY2?

Thank you so much!

Paul Newcome

In that case the [Current Year] formula would be

=IF(AND([Date of Event]@row >= DATE(YEAR(TODAY()) - IF(TODAY() < DATE(YEAR(TODAY()), 9, 1), 1, 0), 9, 1), [Date of Event]@row <= DATE(YEAR(TODAY()) + IF(TODAY() >= DATE(YEAR(TODAY()), 9, 1), 1, 0), 8, 31)), 1)

OshaK

thank you, @Paul Newcome, I will try that. What will happen to the previous years? i need to keep the stats for older than Current years.

Thank you!

Paul Newcome

The data will remain on your metrics sheet. You are simply using a report to automatically grab a new data set from the source sheet based on the "Current Year" box being checked.

OshaK

thank you, @Paul Newcome. I need to retain the charts, not just data, but at least i can speed up the current year charting. Thank you!