Graph Builder: hide X categories with no data

[#1]

Hello everyone,

I have a table with a grouping column and several response variables.

New Table( "Test",
	Add Rows( 7 ),
	New Column( "GROUP",
		Character,
		"Nominal",
		Set Values( {"a", "b", "c", "d", "e", "f", "g"} )
	),
	New Column( "Y",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [7, ., 6, ., 4, ., 2] )
	),
	New Column( "Y 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [7, 6, 2, 3, ., ., .] ),
		Set Display Width( 49 )
	)
)

I would like to create a bar chart with a column switcher.

Graph Builder(
	Size( 527, 448 ),
	Show Control Panel( 0 ),
	Variables(
		X( :GROUP, Order By( :Y, Ascending, Order Statistic( "Mean" ) ) ),
		Y( :Y )
	),
	Elements( Bar( X, Y, Legend( 4 ) ) ),
	Column Switcher( :Y, {:Y, :Y 2} )
)

My problem is that I do not want to display levels with no data. In other words, I do not want to see levels "b", "d" and "f" for Y and levels "e", "f", "g" for Y2.

I tried to use a "where" clause but unfortunately it is not taken into acount by the Column Switcher.

Do you have any idea of how we could do that?

Thanks in advance!

[#2]

Hi Anne,

Your idea that you need a "where" clause or rather a filter of some sort is correct.  Try adding the following line to your graphbuilder script:

Local Data Filter( Add Filter( columns( :Y ), Where( :Y >= 2 & :Y <= 7 ) ) )

I am hoping that a Local Data Filter is okay?

Graph Builder(
	Size( 527, 448 ),
	Show Control Panel( 0 ),
	Variables(
		X( :GROUP, Order By( :Y, Ascending, Order Statistic( "Mean" ) ) ),
		Y( :Y )
	),
	Elements( Bar( X, Y, Legend( 4 ) ) ),
	Column Switcher( :Y, {:Y, :Y 2} ),
	Local Data Filter( Add Filter( columns( :Y ), Where( :Y >= 2 & :Y <= 7 ) ) )

);

[#3]

Thanks for your answer @geoffrey_mann

Unfortunately is it not exactly what I would like to get because when I select a new column with the column switcher, the local data filter is still based on Y.

Of course it is possible to manually change the local data filter but since I can have a quite high number of variables, it would be great to have an automatic way to do that :)

[#4]

Hi Anne,
I see your problem now and am still taking a look at it. I believe this one will take a little coding.

[#5]

Hi @anne_sa ,

As you discovered, Graph Builder observes those categories of your grouping variable and really will insist on plotting them (which is usually a good thing) since it assumes you find value in them since you kept them in your table. In your case with the column switcher, it's not as simple as just deleting those rows since for your other column data do exist. One solution is restructuring your data so that those missing rows can be eliminated. I would do this by stacking:

Tables > Stack

Data Table( "Test" ) << Stack(
	columns( :Y, :Y 2 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

This will return a table like below:

Remove the missing values by right-clicking any of the cells that are missing > select matching. Then, delete those rows. This returns the attached table. Now, make your graph but use a local data filter rather than a column switcher to select what previously was a response from your Label Column:

Graph Builder(
	Show Control Panel( 0 ),
	Variables( X( :GROUP ), Y( :Data ) ),
	Elements( Bar( X, Y, Legend( 4 ) ) ),
	Local Data Filter(
		Add Filter(
			columns( :Label ),
			Where( :Label == "Y" ),
			Display( :Label, Size( 160, 17 ) )
		)
	)
);

Since there are no missing rows, Graph Builder displays only those levels of Group that are populated with data.  You can, of course, generalize this to any number of levels of Y -- simply stack all of them in the initial step.

I hope this helps!

@julian 

[#6]

Thanks @julian  for this suggestion! Indeed it allows to display only levels with data.

The only problem (but maybe I ask too much)  is that we lose the link between rows. For instance if I select the level "a" for Y and if I want to see the corresponding value for Y2, nothing is highlighted when I select Y2 with the local data filter.

Maybe coding the expected behavior is the solution...

[#7]

Hi @anne_sa,

I see just what you mean. It really might come down to coding up something custom, but let me take one more interactive stab at this. Are there always only four levels (albeit different levels) of Group for each Y? If so, here's a potential hack: Graph Builder will maintain the X axis upon column switching, and since you have your levels ordered ascending (thus blank levels are always at the end), we could adjust the axis so that only four levels are showing. You can do this in the axis settings (right click the axis > axis settings), or just by dragging out the end of the axis until the missing levels are hidden). 

Graph Builder(
	Show Control Panel( 0 ),
	Variables( X( :GROUP, Order By( :Y, Ascending, Order Statistic( "Mean" ) ) ), Y( :Y ) ),
	Elements( Bar( X, Y, Legend( 4 ) ) ),
	Column Switcher( :Y, {:Y, :Y 2} ),
	SendToReport( Dispatch( {}, "GROUP", ScaleBox, {Min( -0.5 ), Max( 3.5 ), Inc( 1 ), Minor Ticks( 0 )} ) )
);

Maybe a potential solution in your case?

@julian  

[#8]

Hi @julian!

I really appreciate your efforts to solve my problem! Unfortunately, the number of non-missing levels can be different from one response variable to the next one. However, your last post can definitely be used for the “coding solution”. Changing the axis settings based on the level number is indeed a nice way to display what is expected. I will work in that direction. Thanks for your help!

[#9]

Hi Anne,

I wanted to check back with you to see if you have found a coding solution to your problem.  It took me a while to find the time but I have a coding solution if you still need one.

[#10]

Hi @geoffrey_mann ,

I have written something to basically update the column switcher and change the axis setting (as suggested by @julian ) each time we select a new variable. However it may not be very efficient so of course I am very interested by your solution!