cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
kar456
Level I

Sorting Tabulate by Statistic

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Sorting Tabulate by Statistic

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] ) )

  )

);

View solution in original post

1 REPLY 1
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Sorting Tabulate by Statistic

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] ) )

  )

);