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

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

https://community.smartsheet.com/discussion/comment/241731#Comment_241731

@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

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

https://community.smartsheet.com/discussion/comment/241731#Comment_241731

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

https://community.smartsheet.com/discussion/comment/286808#Comment_286808

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)