topic Graph Builder Bar Graph filter by top 10 Sums in Discussions

Graph Builder Bar Graph filter by top 10 Sums

BSwid — Wed, 13 Mar 2019 17:35:24 GMT

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" )}
		)
	)
)

Re: Graph Builder Bar Graph filter by top 10 Sums

txnelson — Wed, 13 Mar 2019 18:16:58 GMT

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

Re: Graph Builder Bar Graph filter by top 10 Sums

BSwid — Wed, 13 Mar 2019 19:34:10 GMT

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.

Re: Graph Builder Bar Graph filter by top 10 Sums

BSwid — Wed, 13 Mar 2019 20:22:51 GMT

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

Re: Graph Builder Bar Graph filter by top 10 Sums

txnelson — Wed, 13 Mar 2019 20:34:28 GMT

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

Re: Graph Builder Bar Graph filter by top 10 Sums

BSwid — Wed, 13 Mar 2019 21:16:23 GMT

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" )}
		)
	)
)