Filtering on dashboards?

tbittick

I'm sure the answer I'm looking for is answered some place above but it feels like my request is so simple I haven't put my arms around the best way to do this.

Our PMO team has a Dashboard and with one of the Charts they would like the Dashboard user to be able to filter via fiscal year.

Thoughts on an option for doing this?

Screen Shot 2020-08-19 at 1.55.31 PM.png

Uploaded Aug 19, 202086.58 KB

Paul Newcome

@tbittick I would suggest creating a form on a separate sheet and attaching the form to the dashboard.

Then go to the chart data sheet and add a cell that uses a cross sheet reference to pull the most recently "requested" year.

Reference this cell in all of the formulas that drive the data.

User submits form on dashboard requesting a specific year. Form entry goes to form sheet. Cross sheet reference on data sheet pulls year and updates calculations appropriately. User refreshes dashboard and is able to see the requested year's data.

tbittick

Thank you so much Paul. Will keep you posted on our success (and or will be coming back hat in hand with more questions!)

I see from the threads you've really rolled up your sleeves on this one. Thank you for sharing!

Tony

Paul Newcome

Happy to help. 👍️

Andrée Starå

Hi @jwilson10533

I missed your question.

Have you solved it?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Valerie Legris

Paul Newcome

Mar 2, 2020

Ok. On the filter request sheet, you would set it up to have those three columns plus a system generated Created (date) column. Set the form up to include the three searchable columns and have the form populate new entries at the bottom of the sheet.

On the data sheet, you would create 4 new fields in the Sheet Summary: Row, Project Name, Primary Category, and Other Categories.

In the Sheet Summary Row field, you would enter

=COUNT({Filter Request Sheet Created Column})

This will give you the count of the last row populated (which is the most recent request) and will be used to pull the data from that row to your sheet for the filter.

In the Sheet Summary Project Name field:

=INDEX({Filter Request Sheet Project Name Column}, Row#)

Sheet Summary Primary Category field:

=INDEX({Filter Request Sheet Primary Category Column}, Row#)

Sheet Summary Other Categories field:

=INDEX({Filter Request Sheet Other Categories Column}, Row#)

Then you would create a checkbox type column with the following formula:

=IF(AND(FIND([Project Name]#, [Project Name]@row) > 0, FIND([Primary Category]#, [Primary Category]@row) > 0, FIND([Other Categories]#, [Other Categories]@row) > 0), 1)

Finally you would create and activate a filter that only shows rows that are checked.

Then you can display the Filter Request Form and the sheet you are filtering side by side on a dashboard. The user would then fill out the form and submit it then refresh their browser/the dashboard.

If you were fine with people access the Sheet Summary fields and updating those themselves, you could skip the step of creating the form and just instruct people to open the sheet summary, enter their search criteria, save the sheet, then refresh the browser. Saves a fair amount of setup and removes the Sheet Summary Row field.

That is going to depend though on whether or not you want people to have direct access to the sheet or not.

@Paul Newcome Thanks for these details Paul, I was able to set almost everything up! Just one small detail I can't figure out - how do I link the dashboard to always reference the "checked" row? The sorting doesn't refresh automatically, and even with an active filter my target row number changes.

Andrée Starå

Hi @Valerie Legris

Unfortunately, you'll need to update the Dashboard manually or wait for the automatic update if you're using that.

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Valerie Legris

@Andrée Starå When you say update the dashboard, do you mean refresh the browser? If so that's what I did, but it still references the first line of my sheet instead of the "checked" line... Or am I missing something?

Andrée Starå

@Valerie Legris

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Valerie Legris

@Andrée Starå Please see attached the process I would like to activate, and a description of the issue. I noticed from other posts that this seems to be quite a common need, so I've also raised it as an enhancement suggestion. Thanks for your support!

Weekly dashboard issue.pdf

Uploaded Oct 20, 2020730.69 KB

Andrée Starå

@Valerie Legris

I hope you're well and safe!

Excuse the late response.

How did it go? Did you get it working?

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

firestorm

Paul Newcome

Mar 2, 2020

Ok. On the filter request sheet, you would set it up to have those three columns plus a system generated Created (date) column. Set the form up to include the three searchable columns and have the form populate new entries at the bottom of the sheet.

On the data sheet, you would create 4 new fields in the Sheet Summary: Row, Project Name, Primary Category, and Other Categories.

In the Sheet Summary Row field, you would enter

=COUNT({Filter Request Sheet Created Column})

This will give you the count of the last row populated (which is the most recent request) and will be used to pull the data from that row to your sheet for the filter.

In the Sheet Summary Project Name field:

=INDEX({Filter Request Sheet Project Name Column}, Row#)

Sheet Summary Primary Category field:

=INDEX({Filter Request Sheet Primary Category Column}, Row#)

Sheet Summary Other Categories field:

=INDEX({Filter Request Sheet Other Categories Column}, Row#)

Then you would create a checkbox type column with the following formula:

=IF(AND(FIND([Project Name]#, [Project Name]@row) > 0, FIND([Primary Category]#, [Primary Category]@row) > 0, FIND([Other Categories]#, [Other Categories]@row) > 0), 1)

Finally you would create and activate a filter that only shows rows that are checked.

Then you can display the Filter Request Form and the sheet you are filtering side by side on a dashboard. The user would then fill out the form and submit it then refresh their browser/the dashboard.

If you were fine with people access the Sheet Summary fields and updating those themselves, you could skip the step of creating the form and just instruct people to open the sheet summary, enter their search criteria, save the sheet, then refresh the browser. Saves a fair amount of setup and removes the Sheet Summary Row field.

That is going to depend though on whether or not you want people to have direct access to the sheet or not.

Hi @Paul Newcome

How do we handle "searches" for a date range from 1 column? I created "Start" and "End" date fields in the "Search Form" but not sure how to modify the formula for the checkbox.

To be clearer, I want to show results in a dashboard from a date range.

Example, I want to see the dashboard with results between Apr 1st to 8th.

Can you help?

Thanks.

Paul Newcome

@firestorm What is your existing formula that you need to add the date criteria into?

firestorm

Paul Newcome

Apr 9, 2021

@firestorm What is your existing formula that you need to add the date criteria into?

hello @Paul Newcome

This is the formula that I have referred to your earlier post:

=IF(AND(FIND(Member#, [Member]@row) > 0, FIND(Location#, [User's Location(s)]@row) > 0, FIND([Filter Category]#, [Category]@row) > 0), 1)

This has worked perfectly but I have the "date range" search criteria to be included to return dashboard charts only for that date range. There are 2 fields in the search form - "From" & "To", which are also in the Sheet Summary.

Another question: Is it possible to FIND multiple search criteria in 1 field? Example, multiple locations to be "searched"?

I tried to change "FIND" to "CONTAINS" but result is not right :(

=IF(AND(FIND(Member#, [Member]@row) > 0, CONTAINS(Location#, [User's Location(s)]@row), FIND([Filter Category]#, [Category]@row) > 0), 1)

Thank you.

Paul Newcome

@firestorm To include the date criteria, you would drop the range/criteria into the AND function.

=IF(AND(FIND(Member#, [Member]@row) > 0, FIND(Location#, [User's Location(s)]@row) > 0, FIND([Filter Category]#, [Category]@row) > 0, [Date Column]@row>= DATE(2021, 01, 01), [Date Column]@row<= DATE(2021, 01, 31)), 1)

To search for multiple locations, you would use an OR statement

OR(CONTAINS("Location 1", [User's Location(s)]@row), CONTAINS("Location 2", [User's Location(s)]@row), CONTAINS("Location 3", [User's Location(s)]@row))

Then you would use this OR function as one of the "logical statements" of the AND function.

=IF(AND(FIND(Member#, [Member]@row) > 0, OR(........................), FIND([Filter Category]#, [Category]@row) > 0, [Date Column]@row>= DATE(2021, 01, 01), [Date Column]@row<= DATE(2021, 01, 31)), 1)

=IF(AND(FIND(Member#, [Member]@row) > 0, OR(CONTAINS("Location 1", [User's Location(s)]@row), CONTAINS("Location 2", [User's Location(s)]@row), CONTAINS("Location 3", [User's Location(s)]@row)), FIND([Filter Category]#, [Category]@row) > 0, [Date Column]@row>= DATE(2021, 01, 01), [Date Column]@row<= DATE(2021, 01, 31)), 1)