186735_zh-CN

liliconcom

Graph Builder 条形图过滤器按前 10 个总和

我有一个水平堆积条形图，并希望将其限制为 Y 轴类别的前 10 个总和。  

 

该数据包含一年的客户订单。每行都是一个订单行项目，其中包含一个客户、一个价值和一个客户国家（以及其他数据）。x 轴是订单值的汇总统计总和。y 轴是客户名称。并且堆叠（叠加）是由客户国家/地区决定的。

 

我的目标是不显示所有 1,000 名客户，而只显示订单总价值的前 10 名。

目前的脚本如下所示，sales org.是客户国家

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

回复：图表生成器条形图过滤器按前 10 个总和

这就是我最终的结果。谢谢您的帮助。

前20名看起来更好。

 

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

回复：图表生成器条形图过滤器按前 10 个总和

哎呀....我的错误....很高兴你抓住了它！

回复：图表生成器条形图过滤器按前 10 个总和好的问题是“==”，只需要一个“=”回复：图表生成器条形图过滤器按前 10 个总和

我喜欢第二种方法。该脚本似乎在第一段代码之后就停止了。

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

因为我得到了一个新的数据表，但它没有排序并且没有排名。 这是下一组JSL。

回复：图表生成器条形图过滤器按前 10 个总和

我建议您创建一个仅包含前 10 名客户的新数据表。下面的脚本生成子集，然后绘制数据。

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

我没有你的数据副本，所以我没有完全测试脚本，但我认为它应该可以工作。

您还可以修改脚本以创建汇总表的订单号，然后将其连接回原始数据表，并在本地数据过滤器中使用它以允许对订单进行选择。

这是一个脚本，它是如何使用本地数据过滤器创建图表的近似值

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