Sometimes you need to sort charts based on different values rather
than metrics the chart is showing.
For e.g You could be showing total sales
for territories, but you really want to sort the results by Average Discount
offered per territory , or may be average sales per for territory. Or may be
you want to give the user the flexibility to choose how he wants to sort.
Spotfire's DenseRank function and property
controls comes in real handy here.
In my example I mentioned above, I will introduced a
DocumentProperty SortBy
and use expressions to set the value. The Property si exposed as a
drop down controls in the text area.
The four expressions I use are
DenseRank(Avg([Gross Profit]) over ([Territory]))
DenseRank(Sum([Gross Profit]) over ([Territory]))
DenseRank(Avg([Discount]) over ([Territory]))
DenseRank(Sum([Discount]) over ([Territory]))
And the on the chart I introduced the property on the category
axis as $sortby
So my Axis Expression looks like
< ${Sortby} NEST [Territory]>
So now the user can select to Sort the bars by either of the four
options as available in the Drop Down Control.
In case your Sorting rule is fixed, you could directly write the
expression in the Axis Expression and achieve the same.
Clever technique, I will use this.
ReplyDeleteAny idea how you can sort a field with the values from an associated field? For example, it's common in reporting databases to have a string field, let's say Sales Territory, which has a custom sort that's not alphabetical. This field could have hundreds of values which are constantly changing. It's not practical to apply a manual sort to this type of field. Therefore a common practice is to have another field that is Sales Territory Sort that contains an integer field with the sort order. Can this sort order field be used to sort, without introducing NEST into the chart?
Hi Gary, I have not found a solution to it without NEST, but with 6.0 what I have been doing is the integer field I chose to display as a image from link.. so for a bar chart if you go to the Category axis page in properties dialog, select label rendering,then for your integer field, dont provide the url and decrease the size,
DeleteSo it doesnot show anything, but the side effect is there is hypen in the label field.
But for now it works for our users