Why there is an "Empty" bar on my bar chart?

[PBR]

Hello

I have a dataset with columns: Time, Col1, Col2, and Col3 that is externally imported to Spotfire. I want to have a bar chart to show the average value of Col1-3 for the specific selected month through a listbox.

I am using this expression for the category axis: 
 

<If(([Month_String]="${Months}") and ([Year]>=2005) and ([Year]<=2025),
    [Year], NULL) as [Year]>

where Month_String is the calculated column to get the name of each month, ${Months} is a document property of string type, and Year is calculated column as integer.

The y axis expression is: 
 

$map("Avg($esc(${SelectColListBox}))", ",")

Where SelectColListBox is a document property to get the Col name. I am also using an expression to limit the data: 

([Time]>=DocumentProperty("StartDate")) and ([Time]<=DocumentProperty("EndDate"))

Everything is working well except that there is an extra bar on my chart that is named as "Empty" which shows the average of all values within other months. 
Any idea why the bar is there and how to remove that bar? 

[David Boot-Olazabal]

Hi PBR,

You should check the values of the Year column in a table visualization (or Cross Table) to see what actual values have been created that renders it into an (Empty) column.
As you indicate a NULL value within the Category x-axis expression, it might be that some of the combinations of the [Month_String] and [Year] are ending up in that (Empty) bar segment.

If you asses these values and find out it is correct, you can then filter them out in the Limit Data expression by adding onto the existing one.
For instance, it could be something like this:
 

([Time]>=DocumentProperty("StartDate")) and ([Time]<=DocumentProperty("EndDate")) and [Year] is not Null

Kind regards,

David

[Olivier Keugue Tadaa]

Hi PBR,

This below category expression creates an Empty(NULL) value 

If(([Month_String]="${Months}") and ([Year]>=2005) and ([Year]<=2025), [Year], NULL)

To fix this, I'd suggest you create a calculated column (e.g. Display_Year)

Display_Year :::::: If(([Month_String]="${Months}") and ([Year]>=2005) and ([Year]<=2025),[Year], NULL) 

Then use the below on the category axis

<[Display_Year] as [Year]>

Finally, add this to the  expression to limit the data:

([Time]>=DocumentProperty("StartDate")) and ([Time]<=DocumentProperty("EndDate") and  ([Display_Year] is not null))

[PBR]

Thank you David and Olivier. The second method worked for me.

[PBR]

I am wondering if I can concatenate a document property and a calculated column?

I am looking to have the selected month and years on the category axis. 

for example, if I select the January from the list box, then I could have: January 2006, January 2007, ... on my category axis

I cannot concatenate them by any ways:

Display_Year :::::: If(([Month_String]="${Months}") and ([Year]>=2005) and ([Year]<=2025), Concatenate(${Months}, [Year]), NULL)