Showing Cumulative Work Completed Over Time

Curculese

I have projects that are being completed and assigned a completed date. I have a column named "Levels" to filter to the data I want to track. I would like a graph that charts the total number of "Level 2" completions over time.

The way I picture this is a bar chart. The bar has two sections:

1. Everything accumulated previous to the current month.

2. The current month's completions on top of previous completions.

Y-Axis would be total completions… X-Axis would be time. This is a multi-year project, so if there is a better way to display 2023 completions vs 2024 completions (and so on) please give me the idea… I can remove this question and reformat it with a better explanation, if so.

Brian_Richardson

You have a couple of options for this, depending on whether you have the Pivot app in your addons.

Pivot App Method

1. Setup a report first that filters for Level 2 items only
2. Use the Pivot app to setup a pivot. Pivot the Complete Date as Rows and choose Year-Month as the option for that field. Pivot the Levels column as the value, with COUNT as the option. (you can pivot really any column for value, so pick another if it matters to you, we just need a count of some column with values in every row).
3. You'll end up with a Pivot results sheet that has your Level 2 items counted for each year/month. The dates will be in the "Rows" column in the pivot results, and the count will be in a column titled something like CNT-Levels.
4. Add a column to your Pivot results sheet called Auto, properties is Autonumber type of column.
5. Add a column to your pivot results sheet called Cumulative, text/number type, with the following column formula that will add up the numbers from prior months plus current month.

= COUNTIF ( Auto:Auto , <= Auto@row , [CNT-Levels]:[CNT-Levels] )

No Pivot App Method

Steps for that:

1. Add a column to your primary sheet (with all the data) called Year-Month and set a column formula in that column. This formula gives you your dates in the format 2023-03 or 2024-12. = YEAR([Date Completed]@row) + "-" + IF(MONTH([Date Completed]@row)>=10, MONTH([Date Completed]@row, "0"+ MONTH([Date Completed]@row))
2. Create a second sheet with four columns:
   1. Auto: set this column to an Autonumber column.
   2. Months (text/number not date): in this column list all the months and years that you want to chart, in the same format as the Year-Month column in your primary sheet. ie 2023-03. It's important that this match exactly to the format in the Year-Month column in the primary sheet for lookup, and important that both use leading 0s in the month portion of the date so that Smartsheet puts your axis together in the correct order.
   3. Level 2 Complete Count (text/number): add a column formula. = COUNTIFS ( {Year-Month} , Months@row , {Levels} , "Level 2") The {} items are cross-sheet references. You add these by starting to type the formula and then click the Reference Another Sheet link in the formula helper box that pops up. When it pops up, find your primary sheet and then click to select the entire Year-Month or Level column.
   4. Level 2 Complete Cumulative (text/number): add column formula = SUMIF ( Auto:Auto, <= Auto@row, [Level 2 Complete Count]:[Level 2 Complete Count] ) This column looks at the Autonumber and adds up the count for each month, up to and including that row's month.

Chart

Regardless of how you get the data together above, you can then do a report on the results that has both the monthly count and the cumulative count columns. Group on the months and add a Summary for the counts. Then chart that report and the dashboard will pick up your numbers and monthly grouping automatically and chart it.

Brian_Richardson

You have a couple of options for this, depending on whether you have the Pivot app in your addons.

Pivot App Method

1. Setup a report first that filters for Level 2 items only
2. Use the Pivot app to setup a pivot. Pivot the Complete Date as Rows and choose Year-Month as the option for that field. Pivot the Levels column as the value, with COUNT as the option. (you can pivot really any column for value, so pick another if it matters to you, we just need a count of some column with values in every row).
3. You'll end up with a Pivot results sheet that has your Level 2 items counted for each year/month. The dates will be in the "Rows" column in the pivot results, and the count will be in a column titled something like CNT-Levels.
4. Add a column to your Pivot results sheet called Auto, properties is Autonumber type of column.
5. Add a column to your pivot results sheet called Cumulative, text/number type, with the following column formula that will add up the numbers from prior months plus current month.

= COUNTIF ( Auto:Auto , <= Auto@row , [CNT-Levels]:[CNT-Levels] )

No Pivot App Method

Steps for that:

1. Add a column to your primary sheet (with all the data) called Year-Month and set a column formula in that column. This formula gives you your dates in the format 2023-03 or 2024-12. = YEAR([Date Completed]@row) + "-" + IF(MONTH([Date Completed]@row)>=10, MONTH([Date Completed]@row, "0"+ MONTH([Date Completed]@row))
2. Create a second sheet with four columns:
   1. Auto: set this column to an Autonumber column.
   2. Months (text/number not date): in this column list all the months and years that you want to chart, in the same format as the Year-Month column in your primary sheet. ie 2023-03. It's important that this match exactly to the format in the Year-Month column in the primary sheet for lookup, and important that both use leading 0s in the month portion of the date so that Smartsheet puts your axis together in the correct order.
   3. Level 2 Complete Count (text/number): add a column formula. = COUNTIFS ( {Year-Month} , Months@row , {Levels} , "Level 2") The {} items are cross-sheet references. You add these by starting to type the formula and then click the Reference Another Sheet link in the formula helper box that pops up. When it pops up, find your primary sheet and then click to select the entire Year-Month or Level column.
   4. Level 2 Complete Cumulative (text/number): add column formula = SUMIF ( Auto:Auto, <= Auto@row, [Level 2 Complete Count]:[Level 2 Complete Count] ) This column looks at the Autonumber and adds up the count for each month, up to and including that row's month.

Chart

Regardless of how you get the data together above, you can then do a report on the results that has both the monthly count and the cumulative count columns. Group on the months and add a Summary for the counts. Then chart that report and the dashboard will pick up your numbers and monthly grouping automatically and chart it.

Curculese

Thank you for that help. I was able to make some progress with that, but I need to modify the equation going into the original sheet. For months lower than 10, it is not generating a month. Then, for rows that do are not complete (do not have a date) it is giving me #Invalid Data Type.

Brian_Richardson

You probably set the column to date, it needs to be text.

Curculese

I have the new column set up as a Text/Number column. The "Date Completed" column it is referencing is set up as date.