Template Package and Metrics/Dashboard Chart

Amy Evans

Portfolio Metrics.png

Project Intake Sheet.png

Chart.png

Hello,

I'm using the Project Management Office Template set and trying to change the Portfolio Metrics' Project By Status column with new stages. However, I'm not sure how the formula is calculated in the screenshot.

I'd like to link to the Dashboard to create a bar chart (see mockup) of the number of projects each in status, and assuming the Project Intake Sheet will be a growing list of projects. I also included a screenshot of the Project Intake Sheet where the Project Statuses reside.

Your help is much appreciated.

Amy

Genevieve P.

Hi Amy,

It looks like this formula is indeed counting the number of Projects (referenced in the {Project ID} range) with the status specified in that row.

In testing I found that if you had the same project ID listed twice and both rows had the same status, this formula would count that as 2... is this what you wanted? If not, we could add a DISTINCT function into the formula to make sure it's only counting the unique combination of ID & status once:

=COUNT(DISTINCT(COLLECT({Project ID}, {Project Status}, $Label@row)))

(Let me know if this makes sense or if you'd like me to go into further detail).

As far as I can tell, everything is working fine, however you may want to play around with the different settings for the chart widget.

Can I ask how you created this chart? It looks like the X axis isn't reflecting the values in the sheet you're showing us... did you use the same sheet for this chart? It would be helpful to see a screen capture where you open up the setting that says "Edit Data".

If the data isn't displaying as you expect right away, you may also want to try switching rows/columns. Here are a few Help Articles that you may find useful as you continue to build out your dashboard:

• Chart Widget Information
• Dashboard Best Practices Webinar (Preparing Data)
• Dashboard Best Practices Webinar (Building & Designing)

Cheers,

Genevieve

Genevieve P.

Hi Amy,

No problem!

So the $ sign is an "Absolute Reference" locking in the column named Label into the formula. Without the $ mark, if you drag-filled this same formula over to a different column it would shift from referencing Label to referencing the column that's in the same distance from your new column.

See: Absolute References in Formulas

Then the @row function is a handy little row reference. If you have a formula in row one, you might have referenced the cell in the Label column as: $Label1

Notice how the 1 is after the column name? This indicates what row the formula should be looking at in the Label column. I could have $Label3, $Label28, etc.

However, if you replace the number with the @row function this means you don't need to worry about the numbers at all. @row says, look at that column in this row. This means you can copy/paste the formula to any row and it will always look within the row that the formula is in (without needing to read through the sheet and find the correct number). This helps with sheet performance as the formulas can calculate faster since they don't need to read through the sheet.

See: @row function

Hope that helps!

Genevieve

Genevieve P.

Hi Amy,

No problem at all! We can keep these questions here as they go over formula functions, which makes sense for this thread.

1 . @row and TODAY

So for this, the () is part of the TODAY function. TODAY()

This indicates you're just looking at Today, not yesterday or some time in the future. You can compare dates with TODAY by adding either positive or negative numbers (in here).

For example:

=IF([Due Date]@row = TODAY(5), "Due in 5 days")

Adding in a positive 5 means that it's looking to 5 days in the future. If I had -5 in the parentheses like this, TODAY(-5) it would be looking backwards into the past, 5 days ago.

Therefore, when there is nothing (in these) it is not looking to the past or the future, but simply looking at Today: TODAY()

Here's more information on the TODAY function: https://help.smartsheet.com/function/today

For your @row mention, here is how you could adjust the formula to just be looking in that row:

=IF(Complete@row <> 1, IF(TODAY() - [Due Date]@row > 0, "Red", IF(TODAY() - [Due Date]@row > -3, "Yellow", "Green")))

2) Summary of Formula References

For this question, "indented rows" are also known as Child rows. This means that in the Parent row (the main header), you can input a formula that uses our CHILDREN function, like so:

=SUM(CHILDREN())

Similar to the TODAY function, we have nothing (in these) after CHILDREN(). In this case, the reason is that you could reference a different column (in here), but instead, you would want to put this in the same column as the child/indented rows that you're calculating, so you don't need to specify the column at all, leaving these () blank.

Here's information on the CHILDREN Function: https://help.smartsheet.com/function/children

Let me know if there's anything else I can clarify for you. If you're interested in formulas, you may also want to review some of our free, previously recorded Webinars:

• Introduction to Formulas
• Advanced Formulas

Cheers!

Genevieve

Genevieve P.

Hi Amy,

It looks like this formula is indeed counting the number of Projects (referenced in the {Project ID} range) with the status specified in that row.

In testing I found that if you had the same project ID listed twice and both rows had the same status, this formula would count that as 2... is this what you wanted? If not, we could add a DISTINCT function into the formula to make sure it's only counting the unique combination of ID & status once:

=COUNT(DISTINCT(COLLECT({Project ID}, {Project Status}, $Label@row)))

(Let me know if this makes sense or if you'd like me to go into further detail).

As far as I can tell, everything is working fine, however you may want to play around with the different settings for the chart widget.

Can I ask how you created this chart? It looks like the X axis isn't reflecting the values in the sheet you're showing us... did you use the same sheet for this chart? It would be helpful to see a screen capture where you open up the setting that says "Edit Data".

If the data isn't displaying as you expect right away, you may also want to try switching rows/columns. Here are a few Help Articles that you may find useful as you continue to build out your dashboard:

• Chart Widget Information
• Dashboard Best Practices Webinar (Preparing Data)
• Dashboard Best Practices Webinar (Building & Designing)

Cheers,

Genevieve

Amy Evans

Thank you, Genevieve!

Really great response to my question!

Would you mind writing in plain English what this formula means? I'm familiar with COUNT function from Excel, but the "$Label@row" throws me a bit of a curve.

The chart is a dummy mock-up. I was able to create a chart successfully with the correct X/Y values.

Thank you for the article links. I'll review.

AE

Genevieve P.

Hi Amy,

No problem!

So the $ sign is an "Absolute Reference" locking in the column named Label into the formula. Without the $ mark, if you drag-filled this same formula over to a different column it would shift from referencing Label to referencing the column that's in the same distance from your new column.

See: Absolute References in Formulas

Then the @row function is a handy little row reference. If you have a formula in row one, you might have referenced the cell in the Label column as: $Label1

Notice how the 1 is after the column name? This indicates what row the formula should be looking at in the Label column. I could have $Label3, $Label28, etc.

However, if you replace the number with the @row function this means you don't need to worry about the numbers at all. @row says, look at that column in this row. This means you can copy/paste the formula to any row and it will always look within the row that the formula is in (without needing to read through the sheet and find the correct number). This helps with sheet performance as the formulas can calculate faster since they don't need to read through the sheet.

See: @row function

Hope that helps!

Genevieve

Amy Evans

Hello Genevieve,

I reviewed the article references and have more questions. I hope it is okay to add to this string. If you'd like me to create a new question thread, please let me know.

1) @Row Function

=IF(Complete3 <> 1, IF(TODAY() - [Due Date]3 > 0, "Red", IF(TODAY() - [Due Date]3 > -3, "Yellow", "Green")))

What does the "()" represent?

2) Summary of Formula References

Are you able to count up a column's sub-tasks and ignore category headings? For example, if you indent a row to show a task and want to count up all indented rows, what is the best way to do this?

Thank you SO much.

AE

Genevieve P.

Hi Amy,

No problem at all! We can keep these questions here as they go over formula functions, which makes sense for this thread.

1 . @row and TODAY

So for this, the () is part of the TODAY function. TODAY()

This indicates you're just looking at Today, not yesterday or some time in the future. You can compare dates with TODAY by adding either positive or negative numbers (in here).

For example:

=IF([Due Date]@row = TODAY(5), "Due in 5 days")

Adding in a positive 5 means that it's looking to 5 days in the future. If I had -5 in the parentheses like this, TODAY(-5) it would be looking backwards into the past, 5 days ago.

Therefore, when there is nothing (in these) it is not looking to the past or the future, but simply looking at Today: TODAY()

Here's more information on the TODAY function: https://help.smartsheet.com/function/today

For your @row mention, here is how you could adjust the formula to just be looking in that row:

=IF(Complete@row <> 1, IF(TODAY() - [Due Date]@row > 0, "Red", IF(TODAY() - [Due Date]@row > -3, "Yellow", "Green")))

2) Summary of Formula References

For this question, "indented rows" are also known as Child rows. This means that in the Parent row (the main header), you can input a formula that uses our CHILDREN function, like so:

=SUM(CHILDREN())

Similar to the TODAY function, we have nothing (in these) after CHILDREN(). In this case, the reason is that you could reference a different column (in here), but instead, you would want to put this in the same column as the child/indented rows that you're calculating, so you don't need to specify the column at all, leaving these () blank.

Here's information on the CHILDREN Function: https://help.smartsheet.com/function/children

Let me know if there's anything else I can clarify for you. If you're interested in formulas, you may also want to review some of our free, previously recorded Webinars:

• Introduction to Formulas
• Advanced Formulas

Cheers!

Genevieve

Amy Evans

Thanks for the helpful tutorial links and thorough answers.

I guess I do need to brush up on my formulas! :) I'll review those tutorials.

Amy

Genevieve P.

Happy to help! 🙂Please feel free to ask any more of your formula questions when you have them.