878345_en-US

liliconcom

Chart

Hello everyone,

In a graph I would like to be able to display only the first 3 elements according to a percentage criterion -> display only the 3 items with the highest percentage per quarter:

You will find the database attached.

Automation and Scripting Data Exploration and Visualization Re: Graphique

Here is how I created this chart

I sorted the data table by Quarter and descending Pourcentage.

I then created a new column called Rank and set the formula to:

Col Cumulative Sum( 1, :QUARTER )

Then it was a simple matter to create the chart as specified, and to add a Local Data Filter and select the filter to use only the rank values of 1-3

 

 

 

Re: Graphique

Very similar to Jim's solution but with a temporary Transform Column:

 

Graph Builder(
	Transform Column(
		"Transform[POURCENTAGE]",
		Formula( Col Rank( :POURCENTAGE, :QUARTER ) )
	),
	Size( 731, 464 ),
	Show Control Panel( 0 ),
	Variables(
		X( :QUARTER ),
		X(
			:ITEM,
			Position( 1 ),
			Order By( :POURCENTAGE, Descending, Order Statistic( "Mean" ) )
		),
		Y( :POURCENTAGE ),
		Color( :ITEM )
	),
	Elements(
		Bar(
			X( 1 ),
			X( 2 ),
			Y,
			Legend( 14 ),
			Summary Statistic( "Sum" ),
			Label( "Label by Value" )
		)
	),
	Local Data Filter(
		Add Filter(
			columns( :"Transform[POURCENTAGE]"n ),
			Where( :"Transform[POURCENTAGE]"n <= 3 )
		)
	)
);

Re: Graphique

@mmarchandFSLR your code produces a graph that is totally different from mine.

My Chart

and the data table with the Rank column showing the rankings based upon Pourcentage

Your chart run on the same data but very different results

I believe the issue is that your rankings are ranking in ascending order.

 

 

Re: Graphique

@txnelson, I completely flaked on that and forgot that Col Rank chooses the lowest as number 1!  With a small edit, it works as it should.  Thanks for catching that.  Being hasty leads to mistakes.

 

Graph Builder(
	Transform Column(
		"Transform[POURCENTAGE]",
		Formula( Col Rank( 0 - :POURCENTAGE, :QUARTER ) )
	),
	Size( 731, 464 ),
	Show Control Panel( 0 ),
	Variables(
		X( :QUARTER ),
		X(
			:ITEM,
			Position( 1 ),
			Order By( :POURCENTAGE, Descending, Order Statistic( "Mean" ) )
		),
		Y( :POURCENTAGE ),
		Color( :ITEM )
	),
	Elements(
		Bar(
			X( 1 ),
			X( 2 ),
			Y,
			Legend( 14 ),
			Summary Statistic( "Sum" ),
			Label( "Label by Value" )
		)
	),
	Local Data Filter(
		Add Filter(
			columns( :"Transform[POURCENTAGE]"n ),
			Where( :"Transform[POURCENTAGE]"n <= 3 )
		)
	)
);