Fortune Cookie BI: Percentiles, Part Deux

This week’s tip might seem a little difficult. But, TRUST ME, take it one step at a time and you’ll be amazed at how easy it really is!

This technique is a great way to maximize your available space by using variables to change a chart for different looks at your data. Your users will be happy that you put the effort in. Also, it doesn’t have to be limited to percentiles. We can modify it to change your dimensions as needed.

First, review the Quartiles post from last week. The Challenge is to let your user decide on which fractile is important – Quartile? Quintile? Decile? Maybe something else? The answer is it use nested variables.

Now, decide how many ‘versions’ you need. This example shows three: Quartile, Quintile, Decile. Next, create your variables: Settings > Document Properties .

Name the first variable ‘vPercentile’. Leading your variable names with a ‘v’ immediately identifies them as variables when you see them in a list. Listed values must be separated by a semicolon (no spaces).

Name the next variable ‘vPercentile2’. This is a ‘nested’ variable that is used in your expressions. Enter this expression:

= If(vPercentile = 'Quartile', vQuartile,

If(vPercentile = 'Quintile', vQuintile,

If(vPercentile = 'Decile', vDecile)))

Create three more variables, each named for the fractile: vQuartile, vQuintile, vDecile. Enter these expressions as ‘Value’, varying for the name of the fractile.

Notice: NO EQUAL SIGN in these expressions

Quartile:

if(Profit <= fractile(TOTAL Profit, 0.25), 4,

if(Profit <= fractile(TOTAL Profit, 0.5), 3,

if(Profit <= fractile(TOTAL Profit, 0.75), 2,1)))

Quintile:

if(Profit <= fractile(TOTAL Profit, 0.2), 5,

if(Profit <= fractile(TOTAL Profit, 0.4), 4,

if(Profit <= fractile(TOTAL Profit, 0.6), 3,

if(Profit <= fractile(TOTAL Profit, 0.8), 2,1))))

Decile:

if(Profit <= fractile(TOTAL Profit, 0.1), 10,

if(Profit <= fractile(TOTAL Profit, 0.2), 9,

if(Profit <= fractile(TOTAL Profit, 0.3), 8,

if(Profit <= fractile(TOTAL Profit, 0.4), 7,

if(Profit <= fractile(TOTAL Profit, 0.5), 6,

if(Profit <= fractile(TOTAL Profit, 0.6), 5,

if(Profit <= fractile(TOTAL Profit, 0.7), 4,

if(Profit <= fractile(TOTAL Profit, 0.8), 3,

if(Profit <= fractile(TOTAL Profit, 0.9), 2, 1)))))))))

Create a chart. On the General Tab, Window Title, put the following expression:

= If(vPercentile = 'Quartile', 'Quartile',

If(vPercentile = 'Quintile', 'Quintile',

If(vPercentile = 'Decile', 'Decile')))

On the Dimension Tab, use a Calculated Dimension:

= $(vPercentile2)

On the Dimension Tab, enter this expression into the Label field:

= If(vPercentile = 'Quartile', 'Quartile',

If(vPercentile = 'Quintile', 'Quintile',

If(vPercentile = 'Decile', 'Decile')))

Create an Input Box. Right click in the white space, click New Sheet Object > Input Box… Add vPresentation to the Displayed Variables. Change the Input Box properties to match your Caption color.