Filtering on dashboards?

13

Comments

  • 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?



  • Paul Newcome
    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.

    thinkspi.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @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å
    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, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @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!


  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • firestorm
    firestorm
    edited 04/09/21

    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
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

  • firestorm
    firestorm
    edited 04/11/21

    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
    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)

    thinkspi.com