9 Replies Latest reply on Dec 5, 2016 7:52 AM by Joe Oppelt

    Filter for 2 dimensions

    Osoba Netko

      Hello to everybody, I have one tricky poblem.

       

      I would like to create filter with which I could remove rows

      where comparison between seasons is not possible.

       

      I have data like this:

       

      Year
      Chanel
      Value
      2015ABC15
      2015ABC13
      2016ABC21
      2016CNN16

       

      I made this sheet in tableau:

       

      Chanel
      2015
      2016
      % difference
      ABC2821-14.5%
      CNN16-

       

      I would like to have filter with 3 values:

       

      -all

      -comparison not possible       - if selected row CNN will shown

      -comparison possible             - if selected row ABC will be shown  

       

      I haven't find solution to make filter that will remove row if chanel have values for both seasons.

       

      Is that possible? I would really appreciate your help?

        • 1. Re: Filter for 2 dimensions
          Joe Oppelt

          I don't understand your question.  I don't understand how you get your two results from the last two filter values.

           

          Create a sample workbook.  Upload it here, and specify your version.  I'll work with you.

          • 2. Re: Filter for 2 dimensions
            D Barnetson

            Hi Osoba,

             

            I think that I have come up with what you are trying to achieve. Please see the image below and let us know if this is what you are looking to do:

             

            When 'Comparison Not Possible; is selected, the row with ABC is removed, etc.

             

            I've included a packaged workbook (Tableau 10.1), hopefully you are able to open it.

             

            The logic for the calculation that was used to create the 'Compare Result' filter may not be complete, but was enough to create a working filter for the example.

             

            case [Chanel]
            when 'ABC' then if [Year]=2015 or [Year]=2016 then 'Comparison Possible' else 'Comparison Not Possible' end
            when 'CNN' then if [Year]=2015 then 'Comparison Possible' else 'Comparison Not Possible' end

            end

             

            Maybe Joe Oppelt can make the necessary changes to the calculation.

             

            Don

            • 3. Re: Filter for 2 dimensions
              Osoba Netko

              Hi Joe Oppelt,

               

              Thank you for your answer. 

               

              This is printscreen of my dashboard and I have problems to make filter Comparison to work.ScreenShot002.png

               

              I would like that when Comparison Not Possible is selected that only rows

              where comparison is not possible to be visible. That is the case when we don't

              have values for specific channel and both years.

               

              I created sample workbook and uploaded here so you can have a look.

              I have 50 times more data then in this workbook.

               

              I hope my explanation will be helpful to you.

               

              Thank you,

              Mislav

              • 4. Re: Filter for 2 dimensions
                Joe Oppelt

                Where did you upload a workbook, Osoba?   I see D's workbook, but not yours.

                 

                When you are replying, in the upper right corner of the reply editor hit "Advanced editor and add an attachment".  That will put you in the advanced editor.  Then, in the lower right corner of the advanced editor you will see "Attach".  That's how you attach a file.  Make sure it's a packaged workbook (.twbX file), and specify what version you are using.

                • 5. Re: Filter for 2 dimensions
                  Osoba Netko

                  Thank you very much Joe Oppelt for this explanation.

                   

                  I think I attached workbook now.

                   

                  Problem with D's solution is that it will not work always.

                  (because I have other filters on my dashboard too, so

                  I can't "hard coded" names of channels like that in calculation.)

                   

                  I would need some calculation that would say:

                   

                  "FOR EVERY CHANNEL

                       IF (HAS VALUE IN 2015

                            AND HAS VALUE IN 2016)

                      THEN "Comparison possible"

                      ELSE "Comparison is not possible"

                     END"

                   

                  I really have no idea if something like that in possible in tableau.

                   

                  Have a good evening,

                  Osoba

                  • 6. Re: Filter for 2 dimensions
                    Osoba Netko

                    I forgot to write: I am using Tableau 10.0

                    • 7. Re: Filter for 2 dimensions
                      Joe Oppelt

                      You are on one possible right track with your [Metric1 - Comparison Not Possible / Possible 1d] Calc.  If either is null, then not possible.  LOOKUP is a great way to test it out.  However, the logic as it stands now only works for the marks in 2016.  In the attached I added this calc to the text in sheet "X.121. - D 14 - main data table".  You can see what I mean.  We're getting the right value in the 2016 column.  The reason it doesn't work for 2015 is that there is nothing to LOOKUP for -1 in the 2015 column.  We could engineer this differently to pay attention to which column we're in, but it'll get complicated.

                       

                      Look at calc [Any rows here?].  This just tests to see if any rows exist.  I added this to TEXT as well.

                       

                      Now look at [Rows in Both?].  It doesn't care which year has a null.  If we don't get a value of 2, we know it's a row we want to display.  I added this to TEXT as well.

                       

                      Next I made a copy of the sheet.  I dragged the [Rows in Both] calc to filters and selected for value = 0.  Now we only see the two rows you want to see.

                       

                      Keep in mind that when you use a table calc as a filter, you don't actually filter out rows from the table.  You just "hide" rows from the viz, but the non-displayed rows are still in the underlying table.  (So additional totals on the sheet will still include the hidden marks.)  Sometimes this is very useful.  Sometimes it comes back to bite you.  Here it bites you:  The TOTAL line still grabs the total for ALL the rows, not just the two we need to display.

                       

                      If you actually need the total line, we can discuss ways to grab the correct total, but for now, this is an example of how you can filter your sheets the way you want.  (You can probably apply that calc to all your sheets just like you've done with other filters.)

                      • 8. Re: Filter for 2 dimensions
                        Osoba Netko

                        Hi Joe Oppelt,

                         

                        Thank you very much for your effort.

                         

                        I changed your calculation: "Rows in both?" to :

                         

                        if window_sum([Any rows here?]) <2 then "Comparison Not Posible" else "Comparison Possible" END

                         

                         

                        Now what happend: when I add this calculation to filter, and then add it to dashboard.

                        I dont have option for that filter to apply to other sheets on. That is very strange.

                        Can you please have a look on that? The thing is that my client would like to have an

                        option to choose to see: all rows, rows where comparison is possible and rows where it is not.

                         

                        I attached workbook. I tried to put this calc: Rows in both? as filter, but it seems it is possible

                        that this kind of calculation apply only on one sheet and it is not possible to apply it on more.

                         

                        Have a nice day,

                        Mislav

                        • 9. Re: Filter for 2 dimensions
                          Joe Oppelt

                          The calc is a table calc.  It has to be, because you want to look across multiple values of a dimension on the sheet.


                          Because it's a table calc, Tableau does not do "APPLY TO..." on a table calc filter.  It's just a limitation because of the way table calcs work.

                           

                          You can add it individually to each sheet and it will still work uniformly.  You just don't have the nice "apply to..." feature to do it for you.