13 Replies Latest reply on Jan 7, 2019 7:52 AM by Joe Oppelt

    Show Quick Filter values in the sheet else ALL

    Prakash Desai

      Hello,

       

      By default all the values for Region be selected. In that I would need to display Region:All else if few of the values selected from filter, then i would need to display the selected ones, as shown below.

      It will be great if any one has any inputs. Attached workbook.

       

        • 2. Re: Show Quick Filter values in the sheet else ALL
          Joe Oppelt

          You're getting what you see on Sheet 2 because Tableau is cycling through the regions and doing what your calc calls for.  Simple enough.


          But you are actually getting 1-through-5 marks on the sheet, depending on how many regions you selected.


          You want to get it down to one mark, and then you can control "ALL" or a list.

           

          In sheet 2 I made my own calc.  See [List].   This uses the PREVIOUS_VALUE function, and as it cycles through the regions, you can see that you get a growing list until it gets to the last region.

           

          Also look at two other calcs.  [Regions in data] and [Regions on Sheet].  These are LODs that tell me how many regions are in the original data (uses a FIXED LOD), and how many were actually selected  (Uses an EXCLUDE LOD.)

           

          Go to Sheet 2(2).

           

          Here I made [Full List].  It grabs the biggest of the 5 marks, and shoves it on all the marks.  Play with the filter to see how it looks.

           

          Go to Sheet 2(3).

           

          Here I added another calc called [index].  This gives me a 1-through-N count of the marls.  (I took [List] off the sheet at this point.  I don't need it displayed.)  On the next sheet I'm going to move [index] to filters and just display index=1.  We only need one copy of the list.  This is how I get one mark (mentioned earlier.)


          Go to Sheet 2(4).

           

          I put index on filters.  I also made a new version of [Full List].  In there I set its value to ALL when the two LODs are equal.  All Regions are selected, so set the list value to ALL.  Otherwise just grab the [Full List] value.  Play with the filter and see how it looks.

           

          As you noticed in your original Calculation1, you need to run the table calc along [Region].  I had to do that to all the table calcs here too, including [index].

          • 3. Re: Show Quick Filter values in the sheet else ALL
            Prakash Desai

            Joe, need some help in tweaking the logic.

             

            All 3 filters are dependent on each other.

            As long as i am not choosing any value from filter it should always show "All xxxx" and when i choose a value which has more than one value in it then it should show"Multi xxx".

            Basically I want to resemble the same behavior as it does in quick filters in individual sheets.

             

            Appreciate your help!.

            • 4. Re: Show Quick Filter values in the sheet else ALL
              Joe Oppelt

              Let me get some clarification.

              (Using regions and departments might not be useful since in the data here, I think all departments show up in all regions.)

               

              In the attached I have regions and states.

               

              I think what you are asking is if the user selects a single region, but selects all states within that region, you want the list to say that region-X was selected, and "All" states were selected, even though my earlier logic would say that not all states were selected since only a handful of states from the overall list are really selected, even though by the user's impression, all were actually selected.

               

              Please confirm,

              • 5. Re: Show Quick Filter values in the sheet else ALL
                Joe Oppelt

                See attached for a preliminary sheet of what we are going to be looking at

                • 6. Re: Show Quick Filter values in the sheet else ALL
                  Joe Oppelt

                  ANd in the attached I have modified that sheet to tell me how many states exist for each region, and how many were selected for that region.

                   

                  So now the same principle as I did before applies.  I have calcs to tell me about regions selected (displayed in the title) and calcs to tell me about the states selected (displayed on the sheet.)  When the pair of values match, the user has selected ALL.  (whether for state or region) and when they differ we can use the Previous_value method to build the complete list.

                   

                  So what do you want to see here?  I can give each region, and after the region, give a list of that region's states.  Or I can do just about anything you need at this point.

                  • 7. Re: Show Quick Filter values in the sheet else ALL
                    Prakash Desai

                    Sorry if i haven't conveyed properly.

                     

                    Earlier we were displaying the values which were selected in one quick filter in a single sheet. If there are multiple values selected in the drop down then we list them with comma separated. Now instead of displaying all the values with comma separated, if user selects more than 1 value then it should display as "Multiple Dim" instead of Dim Value1, Value2, Value3...

                     

                    By default all values will be selected in the filters.

                    I am expecting below output for each criteria.

                    Lets say Region=East and haven't selected anything in other filters.

                     

                     

                    Region=Central & East, should display as Multiple Regions and haven't touched on other 2 filters yet.

                     

                     

                    Region=East & Central and unselected Connecticut & Delaware for State and not touched Category.

                    If I select Region=East & Central State <>Connecticut & Delaware and Category<>Applicance, Binders and.., Bookcases or any random values.

                     

                     

                    Other Scenario is. Just selected State=Addis Ababa only and see other filters show only relevant values for the selected one.Note that i haven't selected any values from Categories list.

                    For Region: The tricky part here is even though we have only single value, it should display as All Regions. If not achievable then it should display the value as it is International. It will grateful if we have both logics in place.

                     

                    I hope it is clear and let me know for any concerns.

                     

                    Thanks!

                    • 8. Re: Show Quick Filter values in the sheet else ALL
                      Joe Oppelt

                      I've taken it to the next step, and I hope you can use it as a model to go to a third dimension.

                       

                      Start with Sheet 6.  I always start with an expanded version of that I plan to display to make sure my calcs are doing what I want them to do.  So here I have selected regions and states.  (State filter is set to "Relevant Only".)  Notice that if you add International to the list, the count of regions does NOT increase.  That's because no International states are selected yet, do International is filtered out of the sheet.

                       

                      The count of what's on the sheet is counting what is actually on the sheet.  Not what the user checked in the filter list.  That's all we have access to:  The data on the sheet.  Keep that in mind.  If you add International and then select ALL states, International then shows up in the count of Regions on the sheet.

                       

                      Notice that for EAST I have de-selected two states, and the numbers reflect that.

                       

                      Go to Sheet 6(3).  Here I took STATE entirely off the sheet.  The numbers remain in place for us.  That's the beauty of LODs.


                      Go to Sheet 6(4).  I added my Regions in Data and Regions on the sheet calcs.  Here is where you will decide what to display to the user.  If the two region calcs do not match, you can display "Multiple Regions".  If Regions on the sheet = 1, you can display the individual region name as ATTR([Region]).  Or do whatever else needs to be done for that condition.

                       

                      Likewise, if the two state calcs match, then the user has selected ALL states for the selected regions (even if they haven't selected ALL regions!)  If they do not match, then they have selected "Multiple States", but not ALL states.  If the number of states =1 then only one state is selected (and I'd bet that would mean the Regions-on-Sheet count would also be 1.)

                      • 9. Re: Show Quick Filter values in the sheet else ALL
                        Prakash Desai

                        Seems like some of them are working and some are not working as expected. Can you please help me here.

                         

                        Attached the clean slate of workbook with the list of scenarios required.

                         

                        For any selections, I wanted to show the same values in a sheet what I see in the quick filters as is.

                         

                        Should display in all 3 individual sheets as..

                        • (All) -->All Regions/City/Customer
                        • (Multiple Values)-->Multi Regions/City/Customer
                        • Scott Bunn --> Scott Bun(if selected one customer)
                        • Cairo -->Cairo(if selected one city)

                         

                        Thanks!

                        • 10. Re: Show Quick Filter values in the sheet else ALL
                          Prakash Desai

                          Hi Joe, Pls let me know if it is possible or or any work around.

                           

                          Thanks!

                          • 11. Re: Show Quick Filter values in the sheet else ALL
                            Joe Oppelt

                            I'm sorry, Prakash.  I had typed up the following on Friday and I thought I posted it.  Fortunately for me, the web page saved all my typing, and this came back for me when I hit reply!

                             

                            --------

                             

                            This is taking a lot of my time.  Maybe you need a consultant to help you out with this.

                             

                            You've diverged from the way I was doing this in the original workbook we were sharing.  In the attached, I made some changes.

                             

                            Your LOD calcs are doing SUM( ...) inside the calcs.  I took that out of the Region and City calcs.  This required changes to the Region and City Display Name calcs to move the SUM( ..)  inside those calcs.  So you'll see that on the Region and City sheets.

                             

                            Also, in your [City Selected] calc I now have:

                             

                            { FIXED [Region] : COUNTD( [City] ) }

                             

                             

                            You had

                             

                            { FIXED [City] : COUNTD( [City] ) }

                             

                            Your calc would always result in a value of 1 for each city.  My calc tells you how many cities should be associated with each Region.  From there you can see if the user has actually selected fewer than that amount.

                             

                            So right now in the attached, on the city sheet, I have than modified calc displayed.  And I have 6 of the 7 regions selected.  The City sheet shows that ALL cities are selected, and yet the [City on Filter] value is less than the max number of possible cities.

                             

                            There are problems with your data.  Seoul and Pasadena show up in two regions each.  COUNTD will only count these as one city, even though your data thinks they are two separate cities.

                             

                            Is this a real scenario in your actual data?

                             

                            If you take out International, South and West (for testing purposes, because those are the regions impacted by the duplications) then what I've done will work.  If you can have duplicate names across regions, we can address it, but it complicates things.

                             

                            Next question:  How do you want these filters to interact?  If you select only Scott Burns, he shows up in two regions:  Central and North.  Even if you select ALL Regions on the filter, your Region Display name will show "Multi" because only two regions (at most) are now in the underlying table.  What is your expectation for the way Region report will work in this scenario.

                             

                            There are a lot of complications with what you are looking to do, and each one requires thought and calc programming to handle them.  I don't have time to do it all.  (I have my own job, after all!)

                             

                            Oh, another complication.  You have added DATE to this dashboard.  When you filter out 2016, Shenzen goes away.  But the LOD counts still see that city.  For now I've moved the DATE filter into Context.  (Which is why it colored grey) and that tells Tableau to take that filter into account before evaluating FIXED LODs.  Depending on what you want to do with the interaction among the other filters, we may need to do the same with one or more of the other filters.  Again, that will take thought and analysis.

                            • 12. Re: Show Quick Filter values in the sheet else ALL
                              Prakash Desai

                              Sorry for the late reply. I am still having some hurdles in getting the results.

                               

                              can you please help with LOD calc to find the correct number. I wanted to display regions on filter as 7(count of regions from the drop down) instead of 5.

                               

                              Filters: Date=2017; Region=International, North, Pacific, South, West; City=All; Customer Name=All

                              From the above filters/results, i selected Customer Name=Jane Shah, then regions on filter should show as 4 instead of 3.

                               

                               

                              From the above filters/results, i selected City=Seoul, then regions on filter should show as 2 instead of 1

                              And City on filter should show as 3 instead of 1 and city selected should be 1 instead of 2.

                               

                               

                              Thanks!!

                              • 13. Re: Show Quick Filter values in the sheet else ALL
                                Joe Oppelt

                                I am going to repeat something I said in my previous reply.  It is important:

                                 

                                 

                                 

                                There are problems with your data.  Seoul and Pasadena show up in two regions each.  COUNTD will only count these as one city, even though your data thinks they are two separate cities.

                                 

                                Is this a real scenario in your actual data?

                                 

                                If you take out International, South and West (for testing purposes, because those are the regions impacted by the duplications) then what I've done will work.  If you can have duplicate names across regions, we can address it, but it complicates things.

                                 

                                Next question:  How do you want these filters to interact?  If you select only Scott Burns, he shows up in two regions:  Central and North.  Even if you select ALL Regions on the filter, your Region Display name will show "Multi" because only two regions (at most) are now in the underlying table.  What is your expectation for the way Region report will work in this scenario.

                                 

                                There are a lot of complications with what you are looking to do, and each one requires thought and calc programming to handle them.  I don't have time to do it all.  (I have my own job, after all!)

                                 

                                Oh, another complication.  You have added DATE to this dashboard.  When you filter out 2016, Shenzen goes away.  But the LOD counts still see that city.  For now I've moved the DATE filter into Context.  (Which is why it colored grey) and that tells Tableau to take that filter into account before evaluating FIXED LODs.  Depending on what you want to do with the interaction among the other filters, we may need to do the same with one or more of the other filters.  Again, that will take thought and analysis.

                                 

                                I suspect some of that is coming into play with your problem.

                                 

                                Also I will repeat this: Each one requires thought and calc programming to handle them.  I don't have time to do it all.

                                 

                                It looks like your "count in filter" is dependent on other filters, so either you have to put those filters into context, or account for them in an EXCLUDE LOD.  I can only guess at what needs to be done now without a workbook.  But right now I don't have the time to dig into something because I'm swamped at work.