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

    Problem with Todays date reference line and date filter

    Ian Barton

      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
          Sarah Ebreo

          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
            Shinichiro Murakami

            Hi Ian

             

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

             

            Use LOD Overview: Level of Detail Expressions

             

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

             

             

            [Reference Line Max]

            date(dateadd('year',1,datetrunc('year',{fixed : max([Date])}))-1)

             

            [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".

             

             

            Hide Header.

             

             

            Thanks,

            Shin

            1 of 1 people found this helpful
            • 3. Re: Problem with Todays date reference line and date filter
              Ian Barton

              Thanks you!!! Great answers

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

                Hi

                 

                Ian,

                Please give the "Correct Answer" mark to Sarah.

                I took it too much complicated and Sarah's one should be much simpler i this case..

                 

                I need to keep myself think more simple solution.  Sometimes very powerful tool such as LOD makes me lazy and resulted in more complicated solution.

                 

                Thanks,

                Shin