7 Replies Latest reply on Oct 17, 2016 6:47 AM by Yuriy Fal

# How to create dynamic ranges based on percentile values?

I have a bar chart viz where I want to color the bars by the number of orders.  I am limiting the color groups to 5 and want to create ranges that are dynamic (can be applied to another data set with more or less orders).  In the attached example I was able to do this using the RANK_PERCENTILE function.  I labeled the "Color Groups" 1-5.  However I would like for them to be dynamic and display the actual upper and lower ranges (ie 0-215; 216-307; 308-576; 577-777 ect) for the groups instead.  Do anyone know of a way to do this?

• ###### 1. Re: How to create dynamic ranges based on percentile values?

Carol,

You can use the formula below, (I think it's calculating same thing as percentile)

But here you have problem.

Because the text of color Group field is dynamic, the color legend can not be customized.

I mean if you set Red as top category as custom color,

if total number is 100, it says "0-20" and 0-20 is set as "Red"

Then other data which has 200 items, the label says "0-40" and Tableau cannot recognize "0-20" and "0-40" is same color.

*** Also depend on the volume,especially the volume is low, the calculation does not always exactly accurate. ***

[Color Groups (copy)]

IF [Percentile]<= 0.2 THEN

"0-"+str(int(attr({fixed:countd([Customer Name])})/5))

ELSEIF [Percentile]<=0.4 AND [Percentile]>0.2 THEN

str(int(attr({fixed:countd([Customer Name])})/5+1))

+"-"+str(int(attr({fixed:countd([Customer Name])})/5*2))

ELSEIF [Percentile]<=0.6 AND [Percentile]>0.4 THEN

str(int(attr({fixed:countd([Customer Name])})/5*2+1))

+"-"+str(int(attr({fixed:countd([Customer Name])})/5*3))

ELSEIF [Percentile]<=0.8 AND [Percentile]>0.6 THEN

str(int(attr({fixed:countd([Customer Name])})/5*3+1))

+"-"+str(int(attr({fixed:countd([Customer Name])})/5*4))

ELSEIF [Percentile]<=1 AND [Percentile]>0.8 THEN

str(int(attr({fixed:countd([Customer Name])})/5*4+1))

+"-"+str(int(attr({fixed:countd([Customer Name])})))

END

So my recommendation is keep color as same ,but add text label.

Thanks,

Shin

• ###### 2. Re: How to create dynamic ranges based on percentile values?

Hi all,

I took another route, mainly to avoid using

RANK_PERCENTILE() -- a Table Calc --

and to use PERCENTILE() -- an Aggregate -- instead.

This implies using LOD expressions

for binning and boundary calculations.

Hope it could help.

Yours,

Yuri

2 of 2 people found this helpful
• ###### 3. Re: How to create dynamic ranges based on percentile values?

Still Lo~~~ng formula....

Thank you for proving different approach, always helpful.

Regards,

Shin

• ###### 4. Re: How to create dynamic ranges based on percentile values?

I still can't figure out how to get the legend to be dynamic and restricted to 5 ranges when I expand/collapse category or sub-category but this is helpful!  Thanks!

• ###### 5. Re: How to create dynamic ranges based on percentile values?

Hi Carol,

My aggregate calculations are using LOD expressions,

so they are "static" in terms of the Dimensions inside the LOD.

If you would like to collapse-expand a Hierarchy (Products in the example),

you outta resort to Table Calculations -- as long as Compute using Table (Down)

would give your the result you want. Please find the attached (on the ... TC views).

Yours,

Yuri

• ###### 6. Re: How to create dynamic ranges based on percentile values?

Thanks!  I took your label without the LOD expression and moved it to the color shelf and that seem to be exactly what I needed!