How to Track Child (Level 2) Completed Rows Over Time?

Curculese

I have many of these parent (Level 1) rows that are completed as the Level 3 rows underneath it are completed. Under the parent, Level 1, row, is a Level 2 row that is directly tied to certain Level 3 rows. I have completed dates on every row that is actually completed. How would I go about creating a chart that would show the amount of completed Level 2 rows on a per month basis, or on a line chart that increases over time?

Kelly Moore

Hey @Curculese

You will need a helper column to convert the dates in months, so that the data can be collected easily by month. If you plan to span years with the same graph, you might want to consider adding a Year portion to this data.

For month only, create a text/number helper column and use this formula

=MONTH([your completed date]@row)

If you wish to also combine the year with that so you can easily span years, consider using this formula

=YEAR([your completed date]@row)+"-"+MONTH([your completed date]@row)

Once you have the data in a sheet, you can create a report. You will bring in the Helper column as one of the report columns. Filter so that the report filters for your Level 2 rows. Group by your Helper column. Summarize (count) the helper column. You can then use this report as the source for your dashboard graph. Your data will be evergreen always.

https://help.smartsheet.com/articles/2482078-build-a-row-report-with-report-builder#toc-create-a-row-report

Will this work for you?

Kelly

Kelly Moore

@Curculese

To remove the Invalid data try modifying your formula to this:

=IF(ISDATE([your completed date]@row), YEAR([your completed date]@row)+"-"+MONTH([your completed date]@row))

On your report also add another condition to your filter that Year-Month column is not blank.

I'll have to think some on your cumulative number.

Kelly

Kelly Moore

Hey @Curculese

You will need a helper column to convert the dates in months, so that the data can be collected easily by month. If you plan to span years with the same graph, you might want to consider adding a Year portion to this data.

For month only, create a text/number helper column and use this formula

=MONTH([your completed date]@row)

If you wish to also combine the year with that so you can easily span years, consider using this formula

=YEAR([your completed date]@row)+"-"+MONTH([your completed date]@row)

Once you have the data in a sheet, you can create a report. You will bring in the Helper column as one of the report columns. Filter so that the report filters for your Level 2 rows. Group by your Helper column. Summarize (count) the helper column. You can then use this report as the source for your dashboard graph. Your data will be evergreen always.

https://help.smartsheet.com/articles/2482078-build-a-row-report-with-report-builder#toc-create-a-row-report

Will this work for you?

Kelly

Curculese

This is awesome. It did work. I have a couple add on questions to this… How would I modify that function you gave me to remove "Invalid data type" from my graph (some of my projects have not been completed and therefore do not have a completed date, so seen as invalid)? Also, how could I show these numbers as cumulative over time…? Like always include the previous completed work, on top of the work completed in that month? Thank you!

Kelly Moore

@Curculese

To remove the Invalid data try modifying your formula to this:

=IF(ISDATE([your completed date]@row), YEAR([your completed date]@row)+"-"+MONTH([your completed date]@row))

On your report also add another condition to your filter that Year-Month column is not blank.

I'll have to think some on your cumulative number.

Kelly

Curculese

Thank you so much! That did work and is no longer showing in chart…. Please do think on that other part for me!

Kelly Moore

@Curculese

I came up with something. I wasn't sure exactly how you wanted your data displayed - just as a single cumulative bar chart, or as a stacked bar showing previous and this months. The stacked bar will require two helper columns, the single cumulative (since you do have the previous month right next to it) only requires one helper.

To find the rolling cumulative, you have to build the formula in the sheet - a report can't do the math. This formula collects all of the completed data, based on the last day of the month for whatever month. Since the last day of the month varies (28, 30, 31, sometimes 29), we find the Day 1 of the next month then subtract 1 day. I highlighted what I'm talking about. I tried to filter out your blank dates from the collect - let me know if you get errors and I'll have to add more IFERRORS.

*you should be able to copy this formula into Word, etc and do a global replace on what I called a column vs your actual column name. Watch out that the word processor doesn't convert the straight quotes around 'Completed' into curly quotes. Smartsheet will not tolerate curly quote marks.

=IF([Completed Date]@row = MAX(COLLECT([Completed Date]:[Completed Date], [Completed Date]:[Completed Date], ISDATE(@cell), [Completed Date]:[Completed Date], IFERROR(MONTH(@cell),0) = MONTH([Completed Date]@row), [Completed Date]:[Completed Date], IFERROR(YEAR(@cell),0) = YEAR([Completed Date]@row))), COUNTIFS([Completed Date]:[Completed Date],ISDATE(@cell), [Completed Date]:[Completed Date], @cell <= DATE(YEAR([Completed Date]@row), MONTH([Completed Date]@row) + 1, 1) - 1, [your Level column]:[your Level column], 2,[your status column]:[your status column], "Completed"))

Once adding the new formula for the new field, you will need to change the report filter to look for non blanks in that field. You will also use that new field as your Summarize field. This time, you will use SUM. If the calculation works correctly, there should only be one row per month. If there is more, let me know, as that will cause the SUM to overcount by the total showing the row. You should remain grouping on your Year-Month column.

Does this give you what you needed?
Kelly

Curculese

I'm sorry this took awhile to get back to you. I was away from the office for a long weekend. I tried this formula for this block of data… If there was no "Date Completed" I received #Invalid Data Type. Here is the formula I have on the sheet:

=IF([Date Completed]@row = MAX(COLLECT([Date Completed]:[Date Completed], [Date Completed]:[Date Completed], ISDATE(@cell), [Date Completed]:[Date Completed], IFERROR(MONTH(@cell), 0) = MONTH([Date Completed]@row), [Date Completed]:[Date Completed], IFERROR(YEAR(@cell), 0) = YEAR([Date Completed]@row))), COUNTIFS([Date Completed]:[Date Completed], ISDATE(@cell), [Date Completed]:[Date Completed], @cell <= DATE(YEAR([Date Completed]@row), MONTH([Date Completed]@row) + 1, 1) - 1, Levels:Levels, 2, [Total Substations Complete]:[Total Substations Complete], 1))

I tried changing "Level 2" to "2" in the Levels column to see if it would record something and got nothing. I then tried changing 2 to "Level 2" in the formula and that did nothing. Do you see what I am getting wrong? The new column where I put this formula is "Cumulative by Date".

THANK YOU!!!

Kelly Moore

@Curculese

Try this:

=IF(ISDATE([Date Completed]@row),IF([Date Completed]@row = MAX(COLLECT([Date Completed]:[Date Completed], [Date Completed]:[Date Completed], ISDATE(@cell), [Date Completed]:[Date Completed], IFERROR(MONTH(@cell), 0) = MONTH([Date Completed]@row), [Date Completed]:[Date Completed], IFERROR(YEAR(@cell), 0) = YEAR([Date Completed]@row))), COUNTIFS([Date Completed]:[Date Completed], ISDATE(@cell), [Date Completed]:[Date Completed], @cell <= DATE(YEAR([Date Completed]@row), MONTH([Date Completed]@row) + 1, 1) - 1, Levels:Levels, 2, [Total Substations Complete]:[Total Substations Complete], 1)))

Kelly