Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 13, 2019 10:35 AM
(7081 views)

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 SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Mar 13, 2019 10:55 AM
| Last Modified: Mar 13, 2019 11:16 AM
(7077 views)
| Posted in reply to message from BSwid 03-13-2019

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

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

5 REPLIES 5

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Mar 13, 2019 10:55 AM
| Last Modified: Mar 13, 2019 11:16 AM
(7078 views)
| Posted in reply to message from BSwid 03-13-2019

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

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Graph Builder Bar Graph filter by top 10 Sums

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Graph Builder Bar Graph filter by top 10 Sums

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Graph Builder Bar Graph filter by top 10 Sums

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

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Article Labels

There are no labels assigned to this post.