How to avoid null values related bars in cumulative sum Combination chart in Spotfire 6.0

Hello All,

Could someone help me with my problem explained below.

Issue: I want to show only non-null value related bars (i.e. Oct & Nov months) on x-axis of combination chart where y-axis has a calculation of cumulative sum (Note: column is dynamic so had to use case condition, see below for the expression) however due to cumulative sum logic, though x-axis months do not have value, it repeating the previous bars value (i.e. Dec to Sept).

See the screenshot - top graph, i want to avoid yellow highlighted blue bars & green line (dec to sept), by doing this, i dont want to loose orange line which are related to previous year whose data exists up to sept. Bottom graph is JFYI, when i switched to previous year i.e 2015 (blue bar) & 2014 (orange line).

Expression: YearType: Fiscal/Calendar; Year: 2017;

sum(case when '${YearType}'='Fiscal' and [Fiscal]=${Year} then ([${Metric}])
when '${YearType}'='Calendar' and [Calender]=${Year} then ([${Metric}])
end) OVER (AllPrevious([Axis.X])) as [${Metric}]

JFYI, tried using NavigatePeriod function but it do not show cumulative sum bars.

Any input would be greatly appreciated.


Image icon chart.jpg225.63 KB

(2) Answers