3 Replies Latest reply on Jul 19, 2016 12:55 PM by swaroop.gantela

    How to filter some categories on a worksheet, but not others

    Travel Groups Nordstrom Inc

      I have 2 filters for my worksheet (Business Unit and Cost Center). Is there a way to filter all categories (IT Hardware, Office Supplies, Non-Travel F&B, and Mobility) except Corporate Office Printing on my worksheet? I want the user to update the dashboard categories by selecting a Business Unit and Cost Center, but I don’t Corporate Office Printing to be filtered by Business Unit and Cost Center. This is because the user will filter on the Corporate Office Printing dashboard by using store and location instead of BU and CC.

       

      Thank you,

      Lindsay

        • 1. Re: How to filter some categories on a worksheet, but not others
          swaroop.gantela

          If I understood correctly, you are wanting that no matter what filters are selected,

          the Corporate Office Printing Category should show it's total amount.

           

          If that is the case, you can try using a Level of Detail calculation to fix the amount,

          but only for the Category in question:

          IF ATTR([Category])<>"Corporate Office Printing"

          THEN SUM([Value])

          ELSE MIN({Fixed :SUM(IF [Category]="Corporate Office Printing" THEN [Value] END)})

          END

           

          which is saying, for the other categories, just return the Sum of [Value], which will be subject

          to what ever filters are selected.

          But for Corp, find all the lines that are of that category, sum the returned amount

          and fix that quantity. That fixed quantity will be the same for all lines with that category, so I

          took the min to just get the value.

           

          210955fix.png

          • 2. Re: How to filter some categories on a worksheet, but not others
            Travel Groups Nordstrom Inc

            Thank you for your response Swaroop. The only issue I am running into is an error message when trying to use the formula you provided. Any ideas to work around this?

            Thank you,

            Lindsay

            • 3. Re: How to filter some categories on a worksheet, but not others
              swaroop.gantela

              Hmm. That does make things trickier.

              This page has some suggested work-arounds with pros and cons:

              Life before Tableau 9 Level of Detail Calculations | VizPainter

               

              I made an attempt, but it has its drawbacks, and I'm not entirely sure

              if it will work for your real setup. There are very likely other solutions

              that may be more straightforward.

               

              In general, I wanted to join the datasource to itself to get all combinations

              of Categories.

               

              First, I created a new column in my datasource called Key which is just all 1s.

              I used that as the key for the self-join.

              On Sheet3 of the attached, you can see every category split up by Business Unit

              and by Category with their respective values, and then in the last column you

              can see the value of the Duplicate copy of the Value, which is showing the total value.

               

              On Sheet5, I used a calculation to get the Value, basically to say

              if it's the Corp Category then give the total (duplicate) value, if not

              just the regular value which will be subject to the filters on the screen:

               

              IF FIRST()=0 THEN

                  IF ATTR([Category])="Corporate Office Printing"

                  THEN SUM([Value (Sheet1$1)])/COUNTD([Cost Center])

                  ELSE SUM([Value])/COUNT([Value])

                  END

              END

               

              A few notes about this:

              -Because of the self-join, the sum's are inflated and need to be divided by a count.

              You will have to play with the denominator for the Corp Value. I had to divide

              it here by CountD(Cost Center) to get it to work, but it may be different for your dataset.

               

              -Also, because of the items I needed to place on the Details shelf, there were multiple

              copies of values, and so I selected just the first using the First()=0 part.

              This Table Calculation may not be the best for your setup.

               

              -The use of this FIRST() method also affected the formatting of the labels such that

              I couldn't get to show at the end of the bar. I tried using a Dual Axis with text (Sheet4)

              but it wasn't that much better.