Skip to main content

Sorting on different values Dynamically



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.










Labels

Labels:

Comments

  1. AnonymousFebruary 21, 2014 at 10:16 AM

    Clever technique, I will use this.

    Any 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?

    ReplyDelete
    Replies
    1. Data Analytics BloggerFebruary 21, 2014 at 1:58 PM

      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,

      So it doesnot show anything, but the side effect is there is hypen in the label field.

      But for now it works for our users

      Delete

Post a Comment

Popular posts from this blog

Calendar Chart in Spotfire

I recently had a colleague asking me to show him the data in a calendar format.. Something like this I basically used a scatterplot for this.  You can check out the original DXP here  http://bit.ly/spotcalendar Some key settings to make it a calendar. Please note Order date is the date of interest here These expressions are out of the box expressions, no need to handcode them, but select them in the axis value  X Axis    <BinByDateTime([Order Date],"DayOfWeek",0)> Y Axis    Week([Order Date]) Trellis into panels   <BinByDateTime([Order Date],"Year.Quarter.Month",2)> Manual layout 4 X 3 Labels  UniqueConcatenate(DayOfMonth([Order Date])) Shape Tiled Markers Marker by   <UniqueConcatenate([Order Date])> Happy plotting your calendar!!!
7 comments
Read more

Using Accordions in Spotfire Text Areas

Spotfire 6.0 provides great tools for quickly building your dashboard, but here we will learn to spice up things with the help of new 6.0 text area. If you are not aware the new 6.0 Text area supports HTML, Javascript and CSS. This opens up possibilities for doing a lot more with the text area. Some of the basic stuff can be seen here http://spotfire.tibco.com/qrt/UBSAN/presentation.html?pcode=100044   One of the cool things you can do is build an accordion panel in the text area for better utilization of the valuable screen real estate and helping the overall experience.(Watch the 0:15 second video below to see how accordion works - Sorry for the jitter in the video. The screen recorder does not record fast enough) Here is the html behind the text area : Please note that Spotfire controls are automatically created when you add text area controls, like filters, drop down lists etc. <DIV id="myaccordion"> <H3>Analysis Details</...
5 comments
Read more

Spotfire Writing back to a database

Lot of time users need to write back to a database. Spotfire provides different ways to do this, Without going thru the code I am going to talk thru the different options available. But if you need detailed instructions, please leave some comments and will try to provide one 1) Stored Procedures. Advantages : Complex logic is easy to encapuslate in procedures. Also you are not storing username/passwords anywhere  Spotfire can execute stored procedures and these procedures could be fetching data, or can be procedures that can be run Pre or Post running a query. You can then use procedures that basically update your database table or even inserts new rows in tables. When using Spotfire information model it allows you to use these procedures which you can repurpose for updating your database. Spotfire also understands inputs to a procedure and they can be mapped to your marked rows/filtered rows/ properties/constants etc from your analytics. Steps to do that would be ...
14 comments
Read more