6 Replies Latest reply on Jan 4, 2017 1:32 AM by J M

    How do I get LOD to ignore date filter?

    J M

      Hi everyone,

       

      I've searched and tried nearly every recommendation I could find online, but still no luck with the following problem:

       

      I have a table which includes data in roughly the following format:

       

      TeamPersonHeadcount SplitService Date
      Service HoursService Type
      APerson 1.512/1/20161X
      APerson 1.512/2/20162Y
      BPerson 1.512/1/20161X
      BPerson 2112/3/20161X

       

      My tableau file shows the table rolled up by team, and I intend to show (1) the sum of service hours for a 3 month period (using service date) as horizontal bars , and (2) the headcount for the team regardless of service date in the header.

       

      (1) is no problem because it's as simple as adding the service hours measures pill as a sum and then applying a filter to the service date for the 3 month period.

       

      (2) I've created a LOD measure for this: {fixed [Person] : Min([Headcount Split]} When I add the pill to the shelf as Sum, it only shows the correct number when the service date filter is removed.

       

      12-28-2016 11-48-46 AM.png

       

      How can I get this LOD filter to ignore the date filter necessary to calculate the service hours?

      Alternatively, is there a better way to structure this data? I've tried using a linked table which has the headcounts aggregated by team,  but that creates duplicate rows when I add the Service Type pill to color the horizontal bar.

       

      Thanks!

        • 1. Re: How do I get LOD to ignore date filter?
          Sherzodbek Ibragimov

          Hi J M,

          Have you tried to 'add context' that date filter in filter shelf? Just right click date filter and click add to context.

          • 2. Re: How do I get LOD to ignore date filter?
            J M

            Hi Sherzodbek,  thank you for the reply. I tried adding to context, and that had no effect on the results.

            • 3. Re: How do I get LOD to ignore date filter?
              Maciek La

              have you tried something like

              {fixed [Person] : Min({exclude [date field]: min([Headcount Split])})}

               

              you can play with MIN(), MAX(), ATTR() for best results

              • 4. Re: How do I get LOD to ignore date filter?
                Bora Beran

                Adding to context filter would have the opposite effect (if any filters are being ignored, they will stop being ignored)

                 

                You can try writing it the following way

                {fixed [Team] : SUM({include [Person] : MIN([Headcount Split])})}

                 

                This makes sure each person is evaluated within each team e.g. Team A - Person 1, Team B - Person 1 as separate entities then being rolled up to the team by summing.

                 

                You can do the following if you want to compute for Person 1, Person 2 independent of the team and then roll up to the team by summing.

                 

                {fixed [Team] : SUM({fixed [Person] : MIN([Headcount Split])})}

                 

                Tableau inner joins results of the LOD calculation with the query for the sheet. A filter may not affect the LOD expression but it can affect the sheet.

                 

                In your calculation given the sample data you have if you filter out december 1st, there is no Person 1 in Team B anymore. Fixed calculation will have a value for Person 1 but when it tries to join with the view, it won't be able to add the value for Person 1 since the view has filtered that person out. As a result when you try to roll up with sum that person doesn't contribute to the count.

                 

                You will noticed that Team A (given your sample data) won't be affected by the filters if you only exclude december 1st or only december 2nd. Since Person 1 will still be there to allow results to be joined in.

                2 of 2 people found this helpful
                • 5. Re: How do I get LOD to ignore date filter?
                  Sherzodbek Ibragimov

                  JM,

                  Frankly, I didn't quite read your requirement to fully understand your question as I quickly went through it. Please see Bora's reply as it should take care of your issue. If not, please post our sample data and one of us could help on that. Thank you

                  • 6. Re: How do I get LOD to ignore date filter?
                    J M

                    Bora -- This is brilliant, thank you very much for your time, thought, and expertise. I will give this a try.