I have a crosstable that need to highlight (COLOR) the top 2 rows base on the Grand Total of the Rows no matter how I filter it. May I know how can I do it ?

[Wee Loon Goh]

I have a crosstable that need to highlight (COLOR) the top 2 rows base on the Grand Total of the Rows no matter how I filter it. May I know how can I do it ?

[Fredrik Rosell]

Hello,

Here's an example of how that could be done.

Using one of the Spotfire demo analyses, I created a cross table and setup the coloring in this way:

Color the grouping using another expression:

DenseRank(${Axis.Measures.Expression} OVER (All([Axis.Columns])),"desc")

Add Rule > Less than or equal to, Value 2.

So, that expression calculates

a) DenseRank (from the manual: "Returns an integer value ranking of the values in the selected column. The first argument is the column to be ranked.)

of 

b) ${Axis.Measures.Expression}, i.e. the measure I had in the cross table, in my example Sum([Population])

over

c) All([Axis.Columns])), i.e. the columns in by cross table that make up the grand total. 

The coloring rule, Less than or equal to 2, then gives us the top 2 rows.  

Note: The plot at the top is my actual plot, showing the real value I'm interested in. The plot at the bottom is just there to illustrate the resulting values from the coloring expression.

For more information on DenseRank, please refer to https://docs.tibco.com/pub/sfire-analyst/latest/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm#t=ncfe%2Fncfe_ranking_functions.htm&rhsearch=denserank&rhhlterm=denserank&rhsyns=%20