6 Replies Latest reply on Jan 11, 2017 7:21 AM by Chrissy Scott

    Exclude rows with a formula or a filter?

    Chrissy Scott

      Hi All,

       

      Here's my scenario:

      I have one incident ticket that impacts 4 services, but I only want one row for that incident that shows all the impacted services.

       

      I created the following calculated field to give me all impacted services in one field:

      It gives me this:

       

       

      Is there something I can do to the calculated field or a filter I can add to eliminate the top three rows in that group. I want to see this:

       

       

      And, if possible, I don't want to have the Service column there at all. It's there now, because the Service List column doesn't work without it being in the view. The Service List column is using a table calculation.

       

      Workbook attached, but I'm not sure if it's usable because it's an ODBC connection.

       

      Any help is greatly appreciated. Thanks in advance!

        • 1. Re: Exclude rows with a formula or a filter?
          Joe Oppelt

          (Note to self:  V10.0 workbook)

           

          See attached.  In your original sheet I added a calc called INDEX.  This is a helpful trick to let you see what Tableau is doing with the index and how it's walking the table.  I set it so that it restarts every [Number].  And then I added a calc that compares INDEX to the window max of INDEX, again, restarting every NUMBER.

           

          You can see the values in the first sheet.

           

          Once I had it so that I could identify the last entry for each NUMBER, I made a duplicate of the sheet, moved [Calculation2] to filters and filtered on Value = 1.  I removed INDEX from TEXT.


          Also, I right-clicked on the [Service] pill and unchecked "show header".  That eliminates the column entirely, but it still stays on the sheet.

          2 of 2 people found this helpful
          • 2. Re: Exclude rows with a formula or a filter?
            Chrissy Scott

            Worked like a charm; exactly what I needed. I don't understand how, yet, but I'm going to go study those calcs now.

             

            Thank you!

            • 3. Re: Exclude rows with a formula or a filter?
              Joe Oppelt

              Just a little guidance on what's happening under the hood in there...

               

              Tableau can "walk" a table lots of different ways.  Usually, by default, it either works "down" or "across" when evaluating table calcs.  When you rebuild what I did, you will notice that tableau puts [index] on the sheet and defaults it to TABLE(down).  So if [index] is on the text shelf, you will essentially see it number the rows 1 through N down the table.  I like to use an [index] calc to make sure I understand what tableau is doing, and what my instructions to tableau make the software do.  It's something worth putting in your bag of tricks once you get this figured out.

               

              So the settings I used for [index] to get it to count 1-N for each chunk of data was to specify the dimensions, so that it walks through the dimensions in the order I want it (in this case, [number], and within NUMBER cycle through [Service].)  And then I told it to restart for each [Number].  So we get 1-N for each number.  It doesn't matter if there is one row or 100.  Always start at 1 for each [Number].

               

              And then I did a WINDOW_MAX([index]) in the filter calc.  I also did the same settings on that table calc.  Now that calc says, "If my current [index] is equal to the MAX for this [Number] (remember, it restarts for each number), then I'm at the last one.  So remember this one by setting my value to 1."  You can see that on the first sheet.

               

              Now the second sheet.  The really cool thing about using a table calc as a filter is that it doesn't actually discard the other rows, like a quick calc does.  It leaves the whole underlying table intact.  It just DISPLAYS what satisfies the filter.  So when I put it on the filter shelf, I selected for value = 1, and that will only display the rows that are the last one for each [number].  All the other rows are still lurking in the table that supports this sheet.  (Otherwise that PREVIOUS_VALUE calc you are using would have crapped out!)

               

              Table calcs.  They can be your worst enemy or your best friend. 

              • 4. Re: Exclude rows with a formula or a filter?
                Chrissy Scott

                Thank you, Joe, that helped a lot. I read up on a bit over the weekend, too. I've used index a few times but didn't realize all the cool things it can do.

                 

                One thing that's still blowing my mind: how were you able to have the Service field on rows but not actually showing up??? I need to know!

                 

                • 5. Re: Exclude rows with a formula or a filter?
                  Joe Oppelt

                  Right click on the [Service] pill on the ROWS shelf.  There is an option for "Show Header".  By default it gets set to ON.  You can click it off, and the whole column "hides" (but still does its job on the sheet.)

                  • 6. Re: Exclude rows with a formula or a filter?
                    Chrissy Scott

                    Whoa! Look at that! I usually hid the header from the viz itself, never tried it from the pill. Thanks again!