CODEWITHSUNDEEP
expressionvisualizationspotfire
Lee G
Lee G

Reputation: 15

Spotfire bar chart with variable amount of columns used for category axis

I am trying to create a bar chart where the columns used on the category axis are selected by 3 properties set by dropdowns. I want to make it so I can select 'None' on some of the dropdowns when I want fewer than 3 columns to be used.

If I do this with a custom expression <[${BarChartXaxis1}] NEST [${BarChartXaxis2}] NEST [${BarChartXaxis3}]> it only works when 3 columns are selected, and gives a 'Could not find column' error in the visualisation if none is selected for any of the dropdowns.

If have also tried to an expresion which takes into accout when any of the second and third properties are null

CASE
    WHEN ${BarChartXaxis2} IS NULL AND ${BarChartXaxis3} IS NULL THEN <[${BarChartXaxis1}]>
    WHEN ${BarChartXaxis2} IS NULL AND ${BarChartXaxis3} IS NOT NULL THEN <[${BarChartXaxis1}] NEST [${BarChartXaxis3}]>
    WHEN ${BarChartXaxis2} IS NOT NULL AND ${BarChartXaxis3} IS NULL THEN <[${BarChartXaxis1}] NEST [${BarChartXaxis2}]>
    ELSE <[${BarChartXaxis1}] NEST [${BarChartXaxis2}] NEST [${BarChartXaxis3}]>
END

But this gives me an error when trying to save the expression saying the it invalid after <.

Is there an alternative approach or something wrong with the above expressions?

Upvotes: 1

Views: 69

Answers (2)

NHW
NHW

Reputation: 106

The category axis doesn't seem to take any conditional flow statements, but will accept a null value so here is an approach that will take advantage of that.

In the category axis set the axis to NEST based on the document properties:

<$esc(${BarChartXaxis1}) NEST $esc(${BarChartXaxis2}) NEST $esc(${BarChartXaxis3})>

then create a calculated column in the table called 'None' and set the expression to be null

Spotfire create calculated column None

Now in the dropdown for each property when the use selects the 'None' column no nesting will occur in the bar chart. Make sure to uncheck the 'Include (None) alternative' check box in the dropdown Property Control to eliminate that option since it does not work if that option is selected.

Upvotes: 1

Gaia Paolini
Gaia Paolini

Reputation: 1492

I tried different options. There were some initial problems with your expression:

1 - the angled brackets need to be around the whole expression

2 - the property value in the expression is not NULL but an empty string, put quotes around the property names and compare to empty string.

However, the expression parser still does not seem to like NEST in a case statement.

So you could remove the None option, and when you want only one variable, set the three properties all the same and use:

<[${BarChartXaxis1}] NEST [${BarChartXaxis2}] NEST [${BarChartXaxis3}]>

but this is not great as it repeats the variable in the X axis caption.

If you can use IronPython, I would suggest to set this script to run every time each of the three properties change:

you want to define the document property "axisProp" ahead. Then use this property as your X axis.

prop1 = Document.Properties['BarChartXaxis1']
prop2 = Document.Properties['BarChartXaxis2']
prop3 = Document.Properties['BarChartXaxis3']

#From the script, the property does result as None when unset.
if prop2 is None and prop3 is None:
    xaxisProp = "<${BarchartXaxis1}>"
elif prop2 is None and prop3 is not None:
    xaxisProp = "<${BarchartXaxis1} NEST ${BarchartXaxis3}>"
elif prop3 is None and prop2 is not None:
    xaxisProp = "<${BarchartXaxis1} NEST ${BarchartXaxis2}>"
else:
    xaxisProp = "<${BarchartXaxis1} NEST ${BarchartXaxis2} NEST ${BarchartXaxis3}>"

Document.Properties['xaxisProp']=xaxisProp

Upvotes: 1

Related Questions