Chart help with years and % of completed events

userdetails

Hi all,

totally newbie here and need help creating a dashboard chart that will count the number of tasks for each unique year and will have years on the x-axis and completion rate on the Y axis.

I have the following grid as an example:

I need a chart that is showing years starting from 2019 till 2025 and show the completion rate of inspections with a bar chart.

Also need a separate chart showing the on time (inspections completed before due date) and overdue inspections rate.

is this possible to create these two types of charts?

thanks in advance for your help and please explain it to someone who is lost and have no clue about smartsheets. :)

kowal

hi @userdetails,

fastest and easiest way would be please create a helping formula column and call it e.g YEAR in this column use the function =year([inspection completion date]@row) - make it formula column it will show you only the year from the date column. Now in sheet summary add field with Inspections in 2025, or Inspections in 2024…. and in each use formula =countif(Year:Year, 2025) or =countif(Year:Year, 2024) it will count you only the amount of inspections per year.

Hope it helps.

Paul Newcome

To get a chart for a dashboard, I would use a separate sheet with formulas containing cross sheet references.

In your first column would be the years. Next column over to get the overall percent complete would be

=COUNTIFS({Source Sheet Due Date Column}, IFERROR(YEAR(@cell), 0) = [Year Column]@row, {Source Sheet Complete Date Column}, @cell <> "") / COUNTIFS({Source Sheet Due Date Column}, IFERROR(YEAR(@cell), 0) = [Year Column]@row)

And in the next column over would be the on time ones. For this, you will need a helper column in the source sheet. I would use a checkbox column with this in it:

=IF([Complete Date Column]@row <= [Due Date Column]@row, 1)

Of course update column names in the formula to match what you have in your sheet. This will check the box for every row that was completed on time. Moving back to the third column in the helper sheet that will give you the percentage of on time completions…

=COUNTIFS({Source Sheet Due Date Column}, IFERROR(YEAR(@cell), 0) = [Year Column]@row, {Source Sheet Checkbox Column}, @cell = 1) / COUNTIFS({Source Sheet Due Date Column}, IFERROR(YEAR(@cell), 0) = [Year Column]@row)