6 Replies Latest reply on Jan 28, 2017 1:11 PM by dipti.patel.2

    Filter without excluding one value

    Timothy Kravchuk

      Hi there,

       

      I'm working on a report that sources pre-aggregated data. I'll use simple example data as I cannot share our data.
      In terms of the example, we cannot relate [Beverages Consumed] to the [Vehicles Owned], so there's no way to know which car owners consumed what type of beverages. We also can't relate [Vehicle Type] to [Population], except within year. That's why this data is on different rows entirely.

            

      YearPopulationVehicle TypeVehicles OwnedBeverage TypeBeverages Consumed
      2011          1,000,000NULLNULLNULLNULL
      2012          1,250,000NULLNULLNULLNULL
      2013          1,750,000NULLNULLNULLNULL
      2014          1,500,000NULLNULLNULLNULL
      2015          2,000,000NULLNULLNULLNULL
      2011NULLSUV          300,000NULLNULL
      2011NULLSedan          600,000NULLNULL
      2011NULLLuxury            50,000NULLNULL
      2012NULLSUV          200,000NULLNULL
      2012NULLSedan          800,000NULLNULL
      2012NULLLuxury            20,000NULLNULL
      2013NULLSUV          300,000NULLNULL
      2013NULLSedan          500,000NULLNULL
      2013NULLLuxury            60,000NULLNULL
      2014NULLSUV          400,000NULLNULL
      2014NULLSedan          600,000NULLNULL
      2014NULLLuxury            50,000NULLNULL
      2015NULLSUV          300,000NULLNULL
      2015NULLSedan          700,000NULLNULL
      2015NULLLuxury            60,000NULLNULL
      2011NULLNULLNULLBeer   5,000,000
      2011NULLNULLNULLWine   3,000,000
      2012NULLNULLNULLBeer   4,000,000
      2012NULLNULLNULLWine   4,000,000
      2013NULLNULLNULLBeer   6,000,000
      2013NULLNULLNULLWine   3,000,000
      2014NULLNULLNULLBeer   5,000,000
      2014NULLNULLNULLWine   4,000,000
      2015NULLNULLNULLBeer   5,000,000
      2015NULLNULLNULLWine   3,000,000

       

      Our problem is with the filter:

      Using the Vehicle Type filter, we can remove vehicle types from the calculation to omit them, but since we have NULLS for the population rows, Null shows up in the filter selection. If we were to remove the nulls, the calculation cant compute because population values have been removed with the NULLs.

       

      I've tried joining the different data on year, but then we get duplicate counts when summing the measures.

      If we could keep nulls, but not give them as an option on the filter, that would be great. But, anyway I have tried to remove NULLs from the filter options, excludes the NULLs from the calculation.

       

      Is there a way to leave the rows needed for the calculation, but keep it from showing up in the filter?

      If [Filter Values]  represent the filter options, the SQL equivalent would be: [Vehicle Type] = 'NULL' OR [Vehicle Type] IN([Filter Values])

       

      Thanks!

      -Tim

        • 1. Re: Filter without excluding one value
          Keshia Rose

          Hi Tim,

           

          First, to hide the Null value from your filter, make a set on Vehicle Type (right click on it in the dimensions pane > Create > Set) that excludes Null and add it to the filters shelf.

          Then, instead of using the regular Population measure, try making the following calculation:

          {FIXED [Year] :  SUM([Population])}

           

           

          This will keep the population data even if you remove Null from your filter. That way you can use it in your calculation.

           

          Let me know if this works out for you!

           

          Take care,

          Keshia

          1 of 1 people found this helpful
          • 2. Re: Filter without excluding one value
            Timothy Kravchuk

            Hi Keshia,

             

            Thanks for your reply. I have applied the Exclusion to the set, and added that as my filter. I've also adjusted to use fixed population on year. People Per Vehicle now works.

            However, I've tried to take this a step further.

            I need beverages consumed, and it would be great to filter on beverage type. If I bring in SUM([Beverages Consumed]), fixed at [Year] and [Beverage Type]...

             

            Now, I get no values for Fixed Beverages consumed. I think it's because NULL vehicle types are removed.

            When removing the filter, I get values.

            Is my data not suitable for this analysis? I don't want to join because I'm worried about duplicating counts. If I were to join instead of union, is there a way to ensure counts are not duplicated by Tableau?

             

            I've added my workbook to the first post, if you'd like to take a look.

             

            Thanks,

            Tim

            • 3. Re: Filter without excluding one value
              melissa.ladner

              What about custom sql before you get to tableau?

              If you join your data together in custom sql before you get to tableau (based on year) then you will have an information set that is complete and tableau will understand it then.

              So you would join the table to itself in sql based on the year value which will get you unique values for each subset. Then join it again against itself and you will have a row for each combination possible that includes nulls. Then remove all null rows and you should be left with rows that have all three 'sets' of data joined together, in any combination possible. This tableau will then understand and filter accordingly (and your null problem goes away!)

              At least assuming I understand the problem correctly.

              1 of 1 people found this helpful
              • 4. Re: Filter without excluding one value
                Keshia Rose

                Hi Timothy,

                 

                You could remove [Beverage Type] from [Fixed Beverages Consumed] and then add [Beverage Type] to context to get the right numbers. However, you still won't be able to show both the beverage filter and the vehicle filter without nulls on the same worksheet. Since we know that there is no way to relate the beverage and vehicle data together, perhaps it's best to make two different tables and put them on a dashboard. Either that or keep the nulls. Of course you could always make two parameters but I don't think that would work for your use case.

                 

                Take care,

                Keshia

                1 of 1 people found this helpful
                • 5. Re: Filter without excluding one value
                  Timothy Kravchuk

                  Melissa and Keshia,

                   

                  Thank you both for assisting me with this somewhat goofy post. Based on the data structure, this calculation is unintuitive and should not be done. While I do wish there were still a way to get a ratio with both filters in Tableau, there just isn't with the data as it is. Understandable, but still undesirable in our case.

                  On our end, it's a bit more complex. Like here we have three different data sources, but we have many more groupings in our summarized data. The analysis gets complicated fast with mismatched primary keys and duplicated summary data.ck in twerms

                   

                  We've considered the parameters. But Keshia, you're right that they wouldn't work for us, because they would have to be dynamic and contain multiple values. :*(

                   

                  Back to the drawing board!
                  Thanks again,

                  -Tim

                  • 6. Re: Filter without excluding one value
                    dipti.patel.2

                    Hi Tim,

                     

                    I am having the same problem where i am doing union for a dataset where one set is at month level and other at year level and I am getting NULL as a filter for Month and i want the NULL to be selected as well all the time and not show up in the filter.

                     

                    Wondering if you were able to achieve this it wud be helpful if u cud share wat did u do to resolve it?