Dashboard Help

Ty Werven

I want my dashboard to track a couple of things. First the total number of yes and no's in this sheet (form dumps into it). Then I want it to track the "What info is missing" part and a count for how many for each monthly.

I want it to look something like this but with each individual question in "What info is missing" to be its own column if that makes sense.

Here is the formula sheet I have for this dashboard…

Would this be a good way to do this? I have a lot of questions that will be tracked.

Paul Newcome

Exactly how are you setting up your filter?

SSFeatures

Hi Ty,

A good way to do this would be to create two reports to format the data how you want, and then use the Report Widget in your dashboard.

To count the total number of Yes and No's in the sheet, do the following:

1. Create a report.
2. Add the sheet to the report.
3. Select the Yes/No column.
4. Click "Group" then "Group By" the Yes/No column.
5. Click "Summarize" then "Count".

Then create a report widget in the dashboard to access this report.

To create a report for all of the "what info is missing?" responses, do the following:

1. Create a report.
2. Add the sheet to the report.
3. Select the column.
4. Click "Filter" then "What info is missing?" and set it to "is not blank" to filter out any empty rows.

Then create a report widget in the dashboard for this report as well.

The dashboard will look something like this:

Does this look like what you want?

Best,

Paul Newcome

Is the [What info is missing?] column a multi-select dropdown column or single select? How exactly would you want your counts for the Yes/No displayed on the dashboard?

Ty Werven

@Paul Newcome Yes, it is a multi select dropdown column. The whole process is an audit form. So people filling out the form have the option to select multiple things in the "What is missing" column when filling out the form. I will put the yes and no's in just a percentage complete pie chart probably. Or just a sum of yes vs no's using a formula cross referencing sheets.

I am most concerned about seeing monthly misses divided up by those categories. (Ideally we want to track them going down each month). This is why I want to use a graph and not a report if possible.

Ty Werven

@SSFeatures This could be possible I think. One concern is I want it to display divided up by months. It is an audit that I made basically so showing graphically would probably just be more pleasing I would say to people who will be viewing it. Can we divide reports up monthly?

Ty Werven

@SSFeatures @Paul Newcome

Here is a better explanation of what I am trying to do. I want all the options I have in that multi dropdown column to be across on this metric sheet, like pre-surgical drink for example. I have the months in the rows because I want to know the number of times each item across (like pre-surgical drink consumed) was missed each month. I will include my formula below. for some reason it isn't working. Is it because I have multiple things in the "what is missing column"?

Why doesn't the regular sheet have the little arrows in the cells indicating that the column is being pulled into metric sheet? Did I fill out the formula wrong?

Ty Werven

@SSFeatures @Paul Newcome

It seems like I can't use this approach unless you guys know what I am doing wrong? For this test I selected every option in the drop down menu but only the first thing in the list is populating on my metric sheet. Any work around for this or a different formula? Maybe not even possible?

Paul Newcome

For the yes's and no's, my suggestion is a basic report grouped by the yes/no column then use the summary feature in the report to get the counts. From there you can use that report to create your percentage pie chart.

For the COUNTIFS, you will not not be able to use a report because you cannot group on a multi-select column. You will want to incorporate a HAS function into the COUNTIFS along the lines of

=COUNTIFS({Multi-Select Column}, HAS(@cell, "Text to search for), …………………………….)

Ty Werven

@Paul Newcome

Still not sure what is going on. Think I have the formula wrong still. Do I need an AND in here some where to make each cell count for both date and option in drop down? It pulls a "2" in for this formula but it should just be a "1".

Paul Newcome

AND is "built in" to the COUNTIFS function. Try applying a filter to the source sheet that replicates the range/criteria sets and see what comes up.

Ty Werven

@Paul Newcome

Not sure what you mean. How would applying a filter be able to pull the individual counts of things sorted by month and the "What info is missing" column automatically into the metric sheet?

Ty Werven

@Paul Newcome

How about something like this?

Could I do this but instead of the "No" make it months?

Paul Newcome

The filter in the sheet will show which rows are being pulled in by the formula. It is to double check everything to see if there is a back-end issue, and issue with the formula, or potentially a missed row when you were scanning the data visually determining what the count should be.

Ty Werven

@Paul Newcome Could a possible problem be that I am not including the "yes" or "no" within my formuals? right now it is just the date and the info missing. Would we need to add this in for some reason? I had no success finding a problem when using the filters.

Ty Werven

@Paul Newcome I also found this link that talks about a helper column counting the number of entrees within a multi select column. Not sure if this would allow this to work? Or if it simply just returns the number of things that are selected in the column (not the item itself).

https://community.smartsheet.com/discussion/61061/count-of-values-in-a-multi-select-cell

Paul Newcome

Exactly how are you setting up your filter?