expressionvisualizationspotfire

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?


Solution

  • 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.