YTD line chart to show all months regardless of selection

[stevedark]

Hi there,

I think I am missing something obvious here, but I am struggling to get my line chart to behave how I want.

The requirement is to have a year to date chart, starting in April. Regardless of selections the X axis should be static. In the source data (a cross tab XLS) there are zeros for future months.

My first approach was to include zero values, this fixes the axis but shows all lines dropping off when the data goes missing. I tried to address this by then conditionally making the line invisible. I have looked at the line styles and can make the line thinner and dotted - but I can not make it go away completely. Is anyone aware of a style that can acheive this?

The next attempt was to use the static scale options on the chart. To do this I first set up my months as a dual data type, with April being assigned the value 1. The dual type worked fine, but as soon as I set a static scale it changed the legend from showing the text part of the dual data type to showing the numeric representation.

Can anyone advise of a way of getting either of those approaches to work - or is there another way that I have not yet tried?

Thanks in advance,
Steve

[#2 · Accepted solution]

If "YTD" is the field you want to show, and if it is 0 for future months, then

1) Use the following formula instead of YTD to turn 0 into Null: (no line segment shown)

if(YTD >0, YTD)

2) De-select the "suppress zero-values" field in the "presentation" tab to keep the axis complete.

Greetings,

Piet

[stevedark]

Hi Piet,

I posted this some time ago, and haven't given it any thought in a while. Forcing the value to Null works. What was confusing though was that Sum(Value) was returning 0, even when there were no values. Changing the expression to be if(sum(Value) = 0, null(), sum(Value)) worked a treat.

Many thanks.

Steve