Consumer Research: Categorical: Order Descending by Mean Score or by Total Responses

Question

Dear Community,

I have the following script with "Order by Mean Score( 1 )" but does not affect the final result. Does Panel Box has such effects or am I doing something wrong?

Appreciate any idea....

Panel Box(
    "Resorts:YASMIN, Report:GSQ, SubReport:Marketing, Author:SaitCopuroglu,  " || Long Date( Today() ) || " "
     || Format( Time Of Day( Today() ), "h:m" ),
    Data Table( "Veri_SelectionsTest2016RESORTS.jmp" ) << Categorical(
        Grouping Option( Each Individually ),
        X( :Agency ),
        Responses( :Answer Categorical ),
        Frequencies( 0 ),
        Share Chart( 0 ),
        Legend( 0 ),
        Mean Score( 1 ),
        Std Dev Score( 1 ),
        Order by Mean Score( 1 ),
        Local Data Filter(
            Close Outline( 1 ),
            Conditional,
            Add Filter(
                columns( :Resort, :Year, :Record Date ),
                Where( :Resort == "YASMIN" ),
                Where( :Year == 2017 ),
                Where( :Record Date >= 01May2017 & :Record Date <= Today() ),
                Display( :Resort, Size( 123, 21 ), Check Box Display ),
                Display( :Year, Size( 110, 40 ), Check Box Display )
            )
        ),
        SendToReport(
            Dispatch( {}, "Categorical", OutlineBox, {Set Title( "YASMIN RESORT 2017" )} ),
            Dispatch( {}, "(Resort = YASMIN) and (Record Date == 22May2017)", TextBox, {Set Wrap( 1200 )} ),
            Dispatch(
                {},
                "Responses( Answer Categorical ) By Agency",
                OutlineBox,
                {Set Title( "Responses By AGENCY" )}
            )
        )
    )
)

txnelson · Answer

There is not a need to go to Excel to solve the issue.  While the solution isn't the simplest, the output from the Categorical Platform can be converted into a JMP data table directly.  Below is a "nongeneric" script that shows how to do this.  The script can be converted to be a generic piece of code, that would work for most cases.

Names Default To Here( 1 );
dt = Current Data Table();
rpt = dt << Categorical(
	GroupingOption( EachIndividually ),
	X( :SECTION, :MARKET, :COUNTRY ),
	Responses( :VALUE ),
	Frequencies( 0 ),
	ShareChart( 0 ),
	Legend( 0 ),
	MeanScore( 1 ),
	OrderByMeanScore( 1 )
);

dtCountry = New Table( "Country" );

// Build the Country Coulumn
CountryString = Report( rpt )["Responses( VALUE ) By COUNTRY"][GridCellBox( 10 )] << get text;
// Buildthe list of Countries
CountryStringList = {};
WordCNT = 3;
While( Trim( Word( WordCNT, CountryString, "\!n" ) ) != "",
	Insert Into( CountryStringLIst, Trim( Word( WordCNT, CountryString, "\!n" ) ) );
	WordCNT++;,

);
dtCountry << New Column( "Country", character, values( CountryStringList ) );

// Build the Percent Columns
dtCountry << New Column( "Çok iyi", Format( "Percent", 7, 2 ) );
dtCountry << New Column( "İyi", Format( "Percent", 7, 2 ) );
dtCountry << New Column( "Ortalama", Format( "Percent", 7, 2 ) );
dtCountry << New Column( "Zayıf", Format( "Percent", 7, 2 ) );
dtCountry << New Column( "Çok Zayıf", Format( "Percent", 7, 2 ) );
PercentString = Report( rpt )["Responses( VALUE ) By COUNTRY"][GridMultiCellBox( 2 )] << get text;
WordCNT = 1;
While( Trim( Word( WordCNT, PercentString, "\!n" ) ) == "", WordCNT++ );
RowCNT = WordCNT - 1;
While( Trim( Word( WordCNT, PercentString, "\!n" ) ) != "",
	TheLine = Trim( Word( WordCNT, PercentString, "\!n" ) );
	For( i = 1, i <= 5, i++,
		Column( dtCountry, i + 1 )[WordCNT - RowCNT] = Num( Trim( Word( i, Theline, "%" ) ) ) / 100
	);
	WordCNT++;
);

// Add the Total Responses Column
TotalString = Report( rpt )["Responses( VALUE ) By COUNTRY"][GridCellBox( 2 )] << get text;
TotalStringList = {};
WordCNT = 3;
While( Trim( Word( WordCNT, TotalString, "\!n" ) ) != "",
	Insert Into( TotalStringLIst, Num( Trim( Word( WordCNT, TotalString, "\!n" ) ) ) );
	WordCNT++;,

);
dtCountry << New Column( "Total Responses", values( TotalStringList ) );

// Add the Mean Column
MeanString = Report( rpt )["Responses( VALUE ) By COUNTRY"][GridCellBox( 4 )] << get text;
MeanStringList = {};
WordCNT = 3;
While( Trim( Word( WordCNT, MeanString, "\!n" ) ) != "",
	Insert Into( MeanStringLIst, Num( Trim( Word( WordCNT, MeanString, "\!n" ) ) ) );
	WordCNT++;,

);
dtCountry << New Column( "Mean", values( MeanStringList ) );

// Sort the table
dtCountry << Sort( By( :Mean ), Order( Ascending ), Replace Table( 1 ) );

jb · Answer

The issue is that the results are a cross-tab report (not a standard data table).  A partial solution would be to save your results to Excel and then edit them there.  For example:

NamesDefaultToHere(1);
dt = CurrentDataTable();
rpt = dt << Categorical(
	GroupingOption( EachIndividually ),
	X( :SECTION, :MARKET, :COUNTRY ), Responses( :VALUE ),
	Frequencies(0), ShareChart(0), Legend(0),
	MeanScore(1), OrderByMeanScore(1)
);
rpt << SaveExcelFile( "C:\Categorical.jmp",
	SeparateRowsForEachCellStatistic(1)
);

Next steps are to:

* Open the saved Excel file
* Manually delete unneeded Excel columns and rows
* Save the data back into one or more JMP data tables
* Use JMP table sort

saitcopuroglu · Answer

Thank you for your respond.

I attached a simplified data table with the script "categorical". The same question for this specific analysie is, if there is an option for the table arrangement ascending or descending according to total responses and mean score

I tried to "value order" the "COUNTRY" column but whenever the data is updated the value order also...

Thank you in advance

CategoricAnswer.jmp