One of the most requested
tasks is the ability to assign multiple ranges to a column. For example,
suppose you are looking at sales data and you want to look at sales between two
separate ranges? There are a couple
options. You can always create a Tag column and tag the values as either in the
ranges or outside the ranges. This would
require some manual effort in creating the Tag Collection and then tagging the
values.
A quicker option is to use
an expression inside the axes you want to display the column in. For example, if the name of the column we
want multiple ranges on is ‘Total Amount of Purchases' and the two ranges we
wanted were between $5,000 and $20,000 and then between $35,000 and $50,000, we
would build the expression as shown below:
if((20000>[Total
Amount of Purchases] and [Total Amount of Purchases]>5000) or
(50000>[Total Amount of Purchases] and [Total Amount of
Purchases]>35000),[Total Amount of Purchases],null)
If
you add the expression above to an axis in the Expression dialog, it will add a
few parenthesis and show up as it should.
If you use an expression
like above with the values hard coded, its best to hide the Filter from the
Filter Panel, as it may be confusing to the end user.
However, if you use an
expression that does not have hard coded range values, rather they are
calculated using built-in functions, then the ranges will be updated when the
Filter is updated, so it is a good idea to keep showing the Filter.