Product: TIBCO Spotfire®
Why can't I create accurate cumulative bar charts when there is missing data?
When creating a bar chart to show cumulative values there are missing bars. If there is missing data no bar will be displayed. The expected result is that the previous value would "Carry-over" as if it were added to zero.
This is a known Spotfire issue. When you do a bar chart using (AllPrevious([Axis.X])) you can get blanks if there is no information for a given time period. Since there is no data point, Spotfire can't add anything to it to carry over the previous period's total.
There are a few suggested ways to handle this, going from simple to more difficult.
1. Use a line chart instead of a bar chart. This will connect the values and give a slightly better representation.
2. Consider using the combination chart. This would put a line over the bars so that the missing months aren't completely empty.
3. Create a separate custom expression for each category in your bar chart. This can be cumbersome if you have an extensive number of categories, but works well for a limited number.
4. Restructure your data so that there is at least one line per month in each trellis panel. It could have an ID of 0, and then you would use Sum(Count([ID])) so that you don't get an increase in the y-axis totals.
5. Contract with Professional Services to have them create a custom visualization for you.