2 Replies Latest reply on Oct 11, 2015 1:57 PM by chris.jeanty

    Proper Percentile/Quantile Filter

    chris.jeanty

      Hello Community,

       

      Been working on this for the past day and I haven't been able to work exactly how to solve this issue:

      deletejpg.JPG

       

       

      I have a crosstab view in a Tableau worksheet and I need to figure out a way for a given field, Report Priority, how to show only the top 25% (preferably through an adjustable parameter driven filter perhaps).  This needs to be further stratified by team... In other words, for the teams that are available, BI, Strategic and BI other, it needs to calculate  the top 25% report priority values priority within those categories..

       

      I am familiar with the work of Richard Leeke post on the subject, however the formula does not seem compatible with my cross tab text view because each row is its in partition, where as his formula is designed for a visualization like a line that does not have view partition other than what may be delineated by a 'color' mark perhaps.

       

      Any help on this matter would be thoroughly appreciated.

        • 1. Re: Proper Percentile/Quantile Filter
          chris.jeanty

          Hi Community,

           

          So here is my solution:

           

          IF ATTR([Report Priority]) <= aTTR(({Fixed [Team]: CountD([Work Item])}/4))

              //Calucates top 25% of Ope0n available PBIs, by Assigned Team

              Then 'High'

           

          ELSEIF  ATTR([Report Priority]) > aTTR(({Fixed [Team]: CountD([Work Item])}/4)) and

                  ATTR([Report Priority]) <= aTTR(({Fixed [Team]: CountD([Work Item])}/2))

                //Calucates top 25% to 50% of Open available PBIs, by Assigned Team

              Then 'Medium'

           

          ELSEIF  ATTR([Report Priority]) > aTTR(({Fixed [Team]: CountD([Work Item])}/2)) and

                  ATTR([Report Priority]) <= aTTR(({Fixed [Team]: CountD([Work Item])}/(3/4)))

              //Calucates top 50% to 75% of Open available PBIs, by Assigned Team

              Then 'Low'

           

          ELSEIF  ATTR([Report Priority]) > aTTR(({Fixed [Team]: CountD([Work Item])}/(3/4))) and

                  ATTR([Report Priority]) <= aTTR(({Fixed [Team]: CountD([Work Item])})) //Calucates top 75% to 100% of Open available PBIs, by Assigned Team

              Then 'Lowest' 

          END

           

          (I've attached the Updated .twbx)

           

          However I am unable to utilize this new created field as a filter, which was the point of this exercise.  Any thoughts?

          1 of 1 people found this helpful
          • 2. Re: Proper Percentile/Quantile Filter
            chris.jeanty

            Thought I'd share the final solution (credit goes to a close colleague/associate)

             

            The aggregations had to be removed. Particularly, ATTR() logic wrap causes tableau to look at the data in a columnar format rather than at the row level, and thus unable to use the calc as a filter.

             

            IF ([Report Priority]) <= (({Fixed [Team]: CountD([Work Item])}/4)) //Calucates top 25% of Ope0n available PBIs, by Assigned Team

                Then 'High'

            ELSEIF  ([Report Priority]) > (({Fixed [Team]: CountD([Work Item])}/4)) and

                    ([Report Priority]) <= (({Fixed [Team]: CountD([Work Item])}/2)) //Calucates top 25% to 50% of Open available PBIs, by Assigned Team

                Then 'Medium'

            ELSEIF  ([Report Priority]) > (({Fixed [Team]: CountD([Work Item])}/2)) and

                    ([Report Priority]) <= (({Fixed [Team]: CountD([Work Item])}/(3/4))) //Calucates top 50% to 75% of Open available PBIs, by Assigned Team

                Then 'Low'  

            ELSEIF  ([Report Priority]) > (({Fixed [Team]: CountD([Work Item])}/(3/4))) and

                    ([Report Priority]) <= (({Fixed [Team]: CountD([Work Item])})) //Calucates top 50% to 75% of Open available PBIs, by Assigned Team

                Then 'Lowest'    

             

             

            END