M
Marco Russo
I've catched this bug using Excel 2007.
- Create a table using an ODBC query with two value columns with
different ranges of value (i.e. Sales ranging from 100 to 1000 and
Quantity ranging from 1 to 10)
- Apply conditional formatting "Color Scales" on Sales column, by
using Percentile / 10 for minimum value and Percentile / 90 for
maximum alue
- Apply conditional formatting "Color Scales" on Quantity column, by
using Percentile / 10 for minimum value and Percentile / 90 for
maximum alue
- At this point, you have two rules defined - each one work with its
set of values (the values in the column)
- Now, refresh the table (I used a parametrized ODBC query using a
cell value as a parameter to filter data for a single group of data -
changing that cell, the query is refreshed)
- The two rules have been merged into a single one - but this is
wrong, because now the percentile is calculated on the whole set of
values of the 2 columns, and the result is a column green and a column
red
This is a bug because the beavior is different when the mimum and
maximum value for the conditional formatting rule is expressed as
percent - only the Percentile is affected. It seems that the
optimization of rules (merging identical rules into a single one with
a wider area of application) is wrongly executed with Percentile
operations, that should be handled like the Percent case.
I'd like to get a feedback about this...
Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo
- Create a table using an ODBC query with two value columns with
different ranges of value (i.e. Sales ranging from 100 to 1000 and
Quantity ranging from 1 to 10)
- Apply conditional formatting "Color Scales" on Sales column, by
using Percentile / 10 for minimum value and Percentile / 90 for
maximum alue
- Apply conditional formatting "Color Scales" on Quantity column, by
using Percentile / 10 for minimum value and Percentile / 90 for
maximum alue
- At this point, you have two rules defined - each one work with its
set of values (the values in the column)
- Now, refresh the table (I used a parametrized ODBC query using a
cell value as a parameter to filter data for a single group of data -
changing that cell, the query is refreshed)
- The two rules have been merged into a single one - but this is
wrong, because now the percentile is calculated on the whole set of
values of the 2 columns, and the result is a column green and a column
red
This is a bug because the beavior is different when the mimum and
maximum value for the conditional formatting rule is expressed as
percent - only the Percentile is affected. It seems that the
optimization of rules (merging identical rules into a single one with
a wider area of application) is wrongly executed with Percentile
operations, that should be handled like the Percent case.
I'd like to get a feedback about this...
Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo