4 Replies Latest reply on Jan 13, 2017 9:22 AM by Shinichiro Murakami

# Problem with Todays date reference line and date filter

Hi All,

I have a shape chart with dates (6 years) along the x and activities up the y axis. I've also created a vertical reference line for todays date. Everything works fine but if i create an yearly filter for date, if say i select 2015, the chart extends to show todays reference line date. How can this be avoided?

And is there a way to force the chart to show all months (from Jan to December) even if there is no data in Jan or December?

• ###### 1. Re: Problem with Todays date reference line and date filter

Hi Ian,

To answer your first question, you can use a calculated field to only display a reference line for today if the current year is selected in the date field.

1. Create a new calculated field with the formula: IF Year(TODAY()) = YEAR([Date]) THEN TODAY() ELSE NULL END
2. Place the new field onto Detail
3. Right-click the field on Detail and select Exact Date (this should change the field color from blue to green)
4. Add a new reference line for the entire table and select the new calculated field in the Line Value drop down menu

You can also create two other calculated fields to use as reference lines to always show January and December even when there is no data.

Jan Reference Line calculated field formula: MAKEDATE(MAX(YEAR([Date])),1,1)

1. Place the new field onto Detail, right-click and select Continuous
2. Add a new reference line for the entire table and select the new calculated field in the Line Value drop down menu, and Minimum in the next drop down.

Dec Reference Line calculated field formula: MAKEDATE(MAX(YEAR([Date])),12,31)

1. Place the new field onto Detail, right-click and select Continuous
2. Add a new reference line for the entire table and select the new calculated field in the Line Value drop down menu, and Maximum in the next drop down.

You could then remove the line and computation for the Jan and December reference lines so the dates will be displayed on the axis but the reference line would be hidden.

I've also attached a sample workbook. Hope this helps.

Best,

Sarah

• ###### 2. Re: Problem with Todays date reference line and date filter

Hi Ian

To get 1/1 and 12/31 for selected range,

Also to make LOD work only after filtering, change "Date" filter  to context.

[Reference Line Max]

[Reference Line Min]

date(datetrunc('year',{fixed : min([Date])}))

Put these fields into detail and add as reference line, but make it invisible.

(Same setting for Min as well)

Then need to create line for "Today"

But as you experienced, if you add that with ref line, when the range ends before today, the chart still try to show ref line of today.

So create line with other method.

I chose to use text of "|".

Create calculated field with today() and make the chart dual axis with axis synclonized.

And put [Make Line] field on text with set chart type as "Text".

Thanks,

Shin

1 of 1 people found this helpful

• ###### 4. Re: Problem with Todays date reference line and date filter

Hi

Ian,