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

Proper Percentile/Quantile Filter

Hello Community,

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

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

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

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