1 Reply Latest reply on Jul 24, 2017 1:22 PM by Wilson Po

    How can I fill the space from the most recent date value to a time stamp

    Christopher Helmey



      I currently have an Area Chart which is counting Applications as they are approved during the year. I also have a reference line which I am using as a "Todays Date" line. The problem I am running into is if there are no records for the last week then there is a gap from the line to the Area Chart. Is there a way to fill that gap so the area charts extends to the Reference Line?


      I am not able to attach a workbook but I have screenshot of the problem below (I have drawn an arrow to show what gap I am referring to:

        • 1. Re: How can I fill the space from the most recent date value to a time stamp
          Wilson Po

          Hi Chris,

          You might be able to resolve this by amending your Fiscal year dimension with a calculated field:

               IF {MAX([Fiscal year])}<TODAY() THEN

                    IF {MAX([Fiscal year])} = [Fiscal year] THEN TODAY() ELSE [Fiscal year] END

               ELSE [Fiscal year] END

          The calculation above checks to see if the maximum data in the data set is in the past compared to today;  if this is true, it reassigns the last date to be today (as it sounds like the basis of this is to show that the last value in the data set is applicable to today).  There might be some nuances you might want to play with based on how you are defining the last date (especially if other filters or dimensions are involved).  A workbook example would help out to understand if there are more complexities to consider.


          Personally, I would warn against this from a visual best practice perspective.  While I don't fully understand the data or circumstances behind it, masking gaps in data generally ends up being misleading about what the data reports even if it does not seem as clean from a format perspective.  The gap here clearly tells us gaps in the data to be aware of, and the clearest means to convey that is to avoid masking it.