7 Replies Latest reply on Sep 11, 2018 2:00 PM by Okechukwu Ossai

    Elevate Include/Exclude LOD function above Dimension filter

    John Doe

      Hi there -

       

      I'm currently working on a project where a LOD calculation needs to happen before the dimension filter occurs.

       

      Due to the sensitivity of the data, I can't upload my workbook, but I have delineated the problem below:

       

       

      I have a measure called MM which varies by population

      e.g. New York has 150 MM, California has 50, etc..

       

      My data set looks something like this:

       

      Procedure    Spend      State       Gender    ..... MM

      A                     $400         NY             M                 0

      A                     $300         CA             M                 0

      B                      ...              ...              ...                 0

      C                      ...              ...              ...                0

      ..                       ...             ...              ...                 0

      D                      .....           ....              ...                0

      E                      ...            ....               ...                 0

      -                       -              NY              M                 100

      -                       -              NY               F                 50

      -                       -              CA              M                 30

      -                       -              CA               F                 20

       

       

      My tableau table looks like this:

       

      Procedure     Spend         MM LOD       PMPM (Spend/MM)

      A                    1000            200                        5

      B                    1200            200                        6

      C                    1400            200                        7

      D                    2000            200                       10

      E                    2000            200                       10

       

      The LOD I use:

      MM LOD= {Exclude [Procedure]: sum( {Fixed [State], [Gender]: sum(MM)})}

       

       

      The problem:

       

      Let's say I removed procedure E from the view and a sub population, NY Females, received procedure E but not the remaining procedures in the view, the table now updates to:

       

      Procedure     Spend           MM       PMPM (Spend/MM)

      A                    1000            150           6.6

      B                    1200            150           8

      C                    1400            150           9.3

      D                    2000            150          13.3

       

      MM drops from 200 to 150 because it is failing to pull in the 50 MM of NY females due to the dimension filter occurring before my exclude in the LOD function. What I need is for MM to remain at 200.

       

      Would anyone happen to know if there is a way to have the exclude function occur before the dimension filter?

       

      Many thanks for your time.

        • 1. Re: Elevate Include/Exclude LOD function above Dimension filter
          Okechukwu Ossai

          FIXED LODs are higher in the order of operations than dimension filters. You can use FIXED instead. Is there a specific reason you want to use EXCLUDE?

           

          Does this give you what you need?

           

          {Fixed [State], [Gender]: sum(MM)}

          • 2. Re: Elevate Include/Exclude LOD function above Dimension filter
            maneesh.gaddam

            Can you use sum( {Fixed [State], [Gender]: sum(MM)})? I believe it should return the same number as exclude function.

            • 3. Re: Elevate Include/Exclude LOD function above Dimension filter
              John Doe

              Hi Okechukwu -

              Thanks for responding.

               

              When using {Fixed [State], [Gender]: sum(MM)}

              I get something like this

               

              Procedure     Spend         MM LOD       PMPM (Spend/MM)

              A                    1000            200                        ...

              B                    1200            100                        ...

              C                    1400            200                        ....

              D                    2000            130                        ...

              E                    2000            150                         ...

               

              If a sub population did not receive the procedure, the MM is not added. I tried this approach first and it proved to be less stable than the exclude LOD I was using. In the exclude LOD, the issue only arises when a procedure, which a sub population did not receive, is removed from the view. I need the MM even if the population did not receive the procedure, which is why I'm excluding procedure.

              • 4. Re: Elevate Include/Exclude LOD function above Dimension filter
                Okechukwu Ossai

                It's difficult to understand without seeing sample data. You said 50 MM drops off from Procedure D when you remove Procedure E. Do Procedures D and E share that value? Is it possible to mock up a dummy dataset?

                • 5. Re: Elevate Include/Exclude LOD function above Dimension filter
                  John Doe

                  50 MM drops off of all procedures when E is removed b/c sub-population NY Females only received procedure E. When Procedure E is removed, NY Females are not considered and therefore the MM aren't being added. The dummy data set looks like this : 

                   

                  Procedure    Spend      State       Gender    .....   MM

                  A                     $400         NY             M                 0

                  A                     $300         CA             M                 0

                  B                      ...              ...              ...                 0

                  C                      ...              ...              ...                0

                  ..                       ...             ...              ...                 0

                  D                      .....           ....              ...                0

                  E                      ...            ....               ...                 0

                  -                       -              NY              M                 100

                  -                       -              NY               F                 50

                  -                       -              CA              M                 30

                  -                       -              CA               F                 20

                   

                  MM is only populated for the bottom part of the data-set, procedures/spend are unpopulated there.

                  • 6. Re: Elevate Include/Exclude LOD function above Dimension filter
                    Okechukwu Ossai

                    The bottom part doesn't have any Procedure. I can only see "-". Looking at the data, I can't link the values at the bottom to any Procedure.

                     

                    Can you put this data in an Excel spreadsheet in a format that it can be loaded and analyzed in Tableau?

                    • 7. Re: Elevate Include/Exclude LOD function above Dimension filter
                      Okechukwu Ossai

                      Also, from the data you posted, Procedure A has a spend of $400 and $300. How does this become 1000 in the consolidated view?