Sorting Tabulate by Statistic

[#1]

Hello,

I have I am trying to be able to create a tabular table where I can sort the values by the statistic, such as mean value. So for example, for the sample file "Car Poll",  I have the following basic tabulate code:

Tabulate(

       Add Table(

       Column Table( Analysis Columns( :age ), Statistics( Mean ) ),

       Row Table( Grouping Columns( :size ) )

       )

                                        );

Which produces the following pivot table:

                 Age

size         Mean

Large        33.3

Medium    30.7

Small       30.0

but I would like to be able to sort the means in descending value so the first row shows "Medium " and it's values, 2nd row as "Large", and 3rd row as "Small". I know there is a

Sort(By()) function for data tables, but there doesn't seem to be anything similar for tabulate. Does anyone have a suggestion of how I could achieve this?

Thanks!

[#2 · Accepted solution]

I can't find a direct interactive way to sort a tabulate report. However, it acknowledges the value ordering column property.

This code should work to conditionally set value ordering for a column (only tried it in JMP 10):

dt = Open( "$SAMPLE_DATA/Car Poll.jmp" );

col_list = {:size, :age};

Summarize( g = by( col_list[1] ), m = Mean( col_list[2] ) );

col_list[1] << Set Property( "Value Ordering", Eval( g[Rank Index( m )] ) );

dt << Tabulate(

  Add Table(

  Column Table( Analysis Columns( col_list[2] ), Statistics( Mean ) ),

  Row Table( Grouping Columns( col_list[1] ) )

  )

);