1 2 Previous Next 19 Replies Latest reply on Aug 8, 2016 8:00 AM by Simon Runc

    'All' values in filter to clear dashboard

    audreyd

      Dear all,

       

      I'm doing a dashboard that is composed of many worksheets. The idea of the dashboard is to show key figures for one given Shop.

       

      I am using 2 filters: Shop location and Shop Name.

       

      I want the users to be able to either directly choose the Shop Name they want to see, or select the location to filter down their shop name choices and then choose a shop name.

       

      In any case, the dashboard is relevant only if ONE Shop Name is selected.

       

      I would like to create some sort of action that says: if 'All' is selected in the filter 'Shop Name', then clear the dashboard (exclude all values I believe).

       

      But I cannot seem to find a way to do this....

       

      Any idea?

       

      Thanks a lot!

       

      Best

        • 1. Re: 'All' values in filter to clear dashboard
          Simon Runc

          Easiest way to do this is to just not give them the choice!!

           

           

          If you go to the Customize Filter, you can un-tick the 'Show "All" value'

           

          Hope that helps.

          • 2. Re: 'All' values in filter to clear dashboard
            audreyd

            Hi Simon,

            Thank you for your answer. The problem with this is that if I remove the 'all' in the Shop Name, I cannot use the 'Shop Location' filter anymore as it will only show me the location linked to the shop name I chose.

             

            Best

            Audrey

            • 3. Re: 'All' values in filter to clear dashboard
              Simon Runc

              Ah...in that case you can create an Aggregated Calculation something like

              [Single Shop Filter]

              COUNTD([Shop Name]) = 1

               

              and bring this into the filter shelf for any sheets in your Viz, and set to True. This should then collapse the sheets, when more than one shop is selected.

               

              Let me know if that doesn't make sense and I'll work up an example.

              • 4. Re: 'All' values in filter to clear dashboard
                audreyd

                Hi Simon,

                This could work.

                I tried using this formula on the column 'Shop Id' but it still gives me all values despite having 'True' as filtered.

                • 5. Re: 'All' values in filter to clear dashboard
                  Simon Runc

                  Apologies Audrey...my bad, of course as you have Shop ID in the VizLoD each COUNTD would get calculated against each ShopID...so they are all 1, and nothing ever gets filtered out. I'm putting that down to it being a Friday (I think my brain is already fully in weekend mode!!)

                   

                  So what I should have proposed...is the same method, but to use a WINDOW_SUM so we get a COUNTD of all ShopIDs in the Viz

                  [Filter for 1 City]

                  WINDOW_SUM(COUNTD([City])) =1

                   

                  and then set this to True....in my example (attached) the default compute using (Table Down) does the job, but depending how you Viz is set up you may need to define the Table Calcs compute using.

                  1 of 1 people found this helpful
                  • 6. Re: 'All' values in filter to clear dashboard
                    audreyd

                    No worries! The weekend fever is close

                    It might have gotten me too... I can't open your workbook. I tried using your formula in mine though but it seems that I cannot apply it to 'all worksheets using the data source'.

                    • 7. Re: 'All' values in filter to clear dashboard
                      Simon Runc

                      I tried using your formula in mine though but it seems that I cannot apply it to 'all worksheets using the data source'.

                      ...no you won't be able to, as Table Calculation filters don't allow this option. You'll need to add it to each sheet you want to collapse separately.

                       

                      Let me know if you can't get it working and I'll work up a multi-sheet dashboard example (also if you can let me know the version you are using, I can create an open-able version for you)

                      • 8. Re: 'All' values in filter to clear dashboard
                        audreyd

                        Ah this is why. Pity I have more than 20 sheets

                        But even on my sheets, I typed 'WINDOW_SUM(COUNTD([ShopId]))=1' but I only see 'True'. Even if the 'All' is selected in the filter and that I have in my table a lot of ShopIds.

                         

                        We are on 9.2.

                         

                        Does this work on any graph? Even if is not crosstabs?

                        I would not be against a multisheet example. Thank you!

                        • 9. Re: 'All' values in filter to clear dashboard
                          Simon Runc

                          I've no longer got 9.2, but have created the attached in 9.0, so you can open it....

                           

                          So in this I've used City as your ShopID, and state as the selector to filter down the cities.

                           

                          Notice that I have City in the VizLoD of every sheet. We need this so the Table Calc can do it's thing, over this dimension. Without the filter and with all (or multiple cities) selected the Viz looks like a train-wreck! but that's not a problem as the user will only ever see the single city version.

                           

                          In the Cross-Tab, where I have Sub-Category, also, in my VizLoD I needed to change the default compute using on the Table Calc filter to City (else it's doing the WINDOW_SUM over every City/Sub-Cat combination). I also had to hide the sheet titles in the dashboard, else the sheets don't collapse fully. If you need titles, then make them as sheets (with the same City in VizLoD as the others) and add the Table Calc filter and they will collapse and expand too (using containers so they fit over the top of each Viz)...You could even create a Viz, which collapses on the opposite (i.e. when 1 city is selected) and expands when All is selected with some text for the user ('Please select a city'...or similar) for a real polish!!

                           

                          Hope this all makes sense?

                          1 of 1 people found this helpful
                          • 10. Re: 'All' values in filter to clear dashboard
                            audreyd

                            Hi Simon,

                            Sorry for my late answer and thank you very much for your detailed  info! I tried the formula just like it is in your workbook. For some reason, when I select 'true' it takes off some of the data but not all of it and when I put 'false' it shows everything....

                            In any case, I am not sure whether this approach is ok for my workbook where I literally  have more than 20 sheets for my dashboard....

                            • 11. Re: 'All' values in filter to clear dashboard
                              Simon Runc

                              hi Audrey,

                               

                              For some reason, when I select 'true' it takes off some of the data but not all of it

                              ...So this sounds like you have another dimension in your Viz LoD...and need to amend the Compute Using for the Table Calculation (and may need to be set up differently sheet by sheet depending on their VizLoD0. In my example

                               

                               

                              I've set the Compute Using to be the 'thing' I want to count. When you first drag a TC filter onto the filter shelf Tableau will 'guess' at the Compute-Using you want (generally Table Down/Across) and bring you up the options for that. If you then go back into the Compute Using to change this (as I did to change to City) it brings back the filter options again based on the new Compute Using. If True isn't an option (as it might be that the filter is set to one city) you can use the custom value filtering to manually enter True.

                               

                              In terms of another option, not using Table Calculations, it's tricky as you want to assess the Count at a grain lower (all cities) than you have in your Viz. You could use an LoD, but you'd need to have the filter as a context filter (so it got recalculated when the user filtered) which would cause an issue with performance. This way you'd have a formula which wouldn't need to be manually added (and set up) for each sheet, but there would be a hit on performance. I've attached a version of this in case it's of any use.

                               

                              Hopefully you understand the reason Tableau works this way...in 99% of cases when you select 'All' you want to see 'All', and not nothing!!...so it's right we need to find a creative solution for the 1% not the 99%

                              • 12. Re: 'All' values in filter to clear dashboard
                                audreyd

                                hi again! I think I found what was bugging. It is because on my cross table I also have colors based on the type of shop (is it a competitor or is it ours). If I remove the coloring it works but with it it does not.

                                Do you have an idea on how to remove this?

                                 

                                For the context filter, considering the report and the data I believe it will be pretty bad in terms of performance. I would rather stick to using the window sum you proposed.

                                • 13. Re: 'All' values in filter to clear dashboard
                                  Simon Runc

                                  Yes so anything added to Colour/Detail/Size...etc. will add to the Viz LoD. This is similar to the 'Table' worksheet where I have Sub-Category and City in the VizLoD. So you should be able to set it up as per my screen shout above, by setting the Compute Using to be the thing (Shop name in your example).

                                   

                                  Let me know if that doesn't do the trick, and we can delve into the advanced set up.

                                  • 14. Re: 'All' values in filter to clear dashboard
                                    audreyd

                                    I tried setting it up to Shop Name but it didn't change anything

                                    1 2 Previous Next