Graph Builder Bar Graph filter by top 10 Sums

[#1]

I have a horizontal stacked bar graph and want to limit it to just the top 10 sums by Y-axis category.  

The data has a year's worth of customer orders.  Each row is an order line item that has a customer, a value and a customer country (among other data).  The x-axis is summary statistic sum of the order value.  The y-axis is the customer name.  And the stacking (overlay) is by the customer country. 

My goal is to not show all 1,000 customers, but rather just the top 10 by the total of their order values.
 

The script at the moment looks like this, where sales org. is the customer country

Graph Builder(
	Size( 905, 603 ),
	Variables(
		X( :Total Dollars Ordered ),
		Y(
			:Customer,
			Order By( :Total Dollars Ordered, Ascending, Order Statistic( "Sum" ) )
		),
		Overlay( :Sales Organization Code )
	),
	Elements(
		Bar( X, Y, Legend( 4 ), Bar Style( "Stacked" ), Summary Statistic( "Sum" ) )
	),
	SendToReport(
		Dispatch(
			{},
			"graph title",
			TextEditBox,
			{Set Text( "Total Dollars Ordered by Customer" )}
		)
	)
)

[#2 · Accepted solution]

I suggest that you create a new data table that just has the customers that are in the top 10.  The script below generates the subset and then plots the data.

names default to here(1);
dt=current data table();

dtSum == dt << Summary(
	Group( :Customer ),
	Sum( :Total Dollars Ordered ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" )
);

dtSum << sort( by(:Total Dollars Ordered), order(descending),replace table(1));

dtSum << select where(Row()>10);

dtSum << delete rows;

dtFinal = dt<< Join(
	With( dtSum ),
	By Matching Columns( :Customer = :Customer ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

dtFinal << Graph Builder(
	Size( 905, 603 ),
	Variables(
		X( :Total Dollars Ordered ),
		Y(
			:Customer,
			Order By( :Total Dollars Ordered, Ascending, Order Statistic( "Sum" ) )
		),
		Overlay( :Sales Organization Code )
	),
	Elements(
		Bar( X, Y, Legend( 4 ), Bar Style( "Stacked" ), Summary Statistic( "Sum" ) )
	),
	SendToReport(
		Dispatch(
			{},
			"graph title",
			TextEditBox,
			{Set Text( "Total Dollars Ordered by Customer" )}
		)
	)
);

I did not have a copy of your data, so I have not completly tested the script, but I think it should work.

You could also modify the script to create the order number of the summary table, and then join that back into the original data table, and use that in a local data filter to allow for selection on the order.

Here is a script that is an approximation of how to create the chart using a local data filter

names default to here(1);
dt=current data table();

dtSum == dt << Summary(
	Group( :Customer ),
	Sum( :Total Dollars Ordered ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 )
);

dtSum << sort( by(:Total Dollars Ordered), order(descending),replace table(1));

dtSum << New Column("Rank", formula(Row()));
dtSum:Rank << delete property("formula");
dtSum << delete columns({"Total Dollars Ordered", "N Rows"})

dt<< Update(
	With( dtSum ) ,
	Match Columns( :Customer == :Customer )
);


dt << Graph Builder(
	Size( 905, 603 ),
	Variables(
		X( :Total Dollars Ordered ),
		Y(
			:Customer,
			Order By( :Total Dollars Ordered, Ascending, Order Statistic( "Sum" ) )
		),
		Overlay( :Sales Organization Code )
	),
	Elements(
		Bar( X, Y, Legend( 4 ), Bar Style( "Stacked" ), Summary Statistic( "Sum" ) )
	),
	SendToReport(
		Dispatch(
			{},
			"graph title",
			TextEditBox,
			{Set Text( "Total Dollars Ordered by Customer" )}
		)
	),
	Local Data Filter(
		Add Filter( columns( :Rank ), Where( :Rank >= 1 & :Rank <= 11 ) )
);

[#3]

I like the 2nd approach.  The script seems to be stopping  right after the first section of code.

dtSum == dt << Summary(
	Group( :Customer ),
	Sum( :Total Dollars Ordered ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 )
);

Because I'm getting a new data table but it isn't sorted and doesn't have rank. Which is the next set of JSL.

[#4]

Okay the problem is "==", just need one "="

[#5]

Oops....my error....glad you caught it!

[#6 · Accepted solution]

Here's what I ended up going with.  Thank you for your help.

Top 20 looked nicer.

names default to here(1);
dt=current data table();

//Summarize the Total Dollars Ordered by Customer
dtSum = dt << Summary(
	Group( :Customer ),
	Sum( :Total Dollars Ordered ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 )
);

//Sort the new data table
dtSum << sort( by(:Total Dollars Ordered), order(descending),replace table(1));

//Add Customer Sales Rank
//because the data is sorted the rank is the row number
dtSum << New Column("Customer Sales Rank", formula(Row()));
dtSum:Customer Sales Rank << delete property("formula");
dtSum << delete columns({"Total Dollars Ordered", "N Rows"});

//add the Customer Sales Rank back to the original data table
dt << Update(
	With( dtSum ) ,
	Match Columns( :Customer == :Customer )
);

//close the helper table
close(dtSum);

//Create the graph, horizontal bar graph, highest sales at the top, 
dt << Graph Builder(
	Size( 966, 804 ),
	//hide the control panel
	Show Control Panel( 0 ),
	Variables(
		X( :Total Dollars Ordered ),
		Y(
			:Customer,
			Order By( :Total Dollars Ordered, Ascending, Order Statistic( "Sum" ) )
		),
		Overlay( :Sales Organization Code )
	),
	Elements(
		Bar(
			X,
			Y,
			Legend( 4 ),
			Bar Style( "Stacked" ),
			Summary Statistic( "Sum" ),
			Label( "Label by Percent of Total Values" )
		)
	),
	Local Data Filter(
		//hide the local filer
		Close Outline(1),
		Add Filter(
			columns( :Customer Sales Rank ),
			Where( :Customer Sales Rank >= 1 & :Customer Sales Rank <= 20 )
		)
	),
	SendToReport(
		Dispatch(
			{},
			"graph title",
			TextEditBox,
			{Set Text( "Total Dollars Ordered by Customer" )}
		)
	)
)