Contents x
      InsertDates
      • 4 Minutes to read
      • Print
      • Dark
        Light
      • PDF
      Contents

      InsertDates

      • 4 Minutes to read
      • Print
      • Dark
        Light
      • PDF
      Article summary

      Inserts missing dates (and rows) into a time-based result set or extends result set back or forwards in time

      Purpose

      Use this function to:

      • Create consistent time-series data in sparse or sporadic data sets
      Sequencing of processing
      If using InsertDates in a multi-function profile, make sure it is the first post-process.
      Working with InsertDates

      InsertDates is a flexible and powerful function that can significantly alter MFP results and affect all other post-processing.    Please be sure to develop analyses that use this functionality in stages as it is easy to specify a configuration that prevents post-processing from running. 

      If the report won’t run, try turning off Post Processing to see the raw results that are being fed to the post-processor.

      Be mindful when sharing reports generated using InsertDates that the intended audience has appropriate transparency of how the report was calculated.

      Usage Notes
      • InsertDates is often used in conjunction with MissingValues.
      • InsertDates will over-ride any row limits set using Multi-Function Profile Options
      • If the Date Dimension field in a Multi-Function profile is switched out for another field, remember to update any InsertDates of FormatDate processes that were accessing the old field.


      Parameters



      ParameterDescription
      ColumnRequired.  The Date Column in which to insert dates.  Must be a date field, or "first date error" will be generated.
      Start of Month

      Required. 

      N / Y

      • Determines whether or not to extend the first date back in time.
      • If N, does not extend back.
      • How far it extends back depends on the value of [Start Date Field] parameter.
      Start Date Field

      Optional.  Only used if [Start of Month] = Y.

      Must be in format [Table].[Field]


      If [Start Date Field] = ““  InsertDates column extends back to first day of first month of raw result set.

      FirstDate=19/02/2019   ,  [Start Date Field] = ""   

      InsertDates extends back to 01/02/2019


      If [Start Date Field] = [linkedtable].[discretedatefield],  InsertDates column extends back to the closest date in [linkedtable].[discretedatefield] that is  earlier than first date in raw result set.

      FirstDate=19/02/2019   ,  [Start Date Field] = "[Tab].[Dates]"

      [Tab].[Dates] = 06/02/2019, 13/02/2019, 20/02/2019...   

      InsertDates extends back to 13/02/2019


      [Start Date Field] must refer to a DISCRETE DATE TIME FIELD in a table that is linked to the Multi-Function Profile dimension table.

      End of Month

      Required.

      N / Y

      • Determines whether or not to extend the last date forward in time.
      • If N, does not extend forwards.
      • How far forwards it extends depends on the value of [End Date Field] parameter.
      End Date Field

      Optional.  Only used if [End of Month] = Y.

      Must be in format [Table].[Field]

      If [End Date Field] = ““  InsertDates column extends forward to last day of last month of raw result set.

      LastDate=19/02/2019   ,  [End Date Field] = ""   

      InsertDates extends forward to 28/02/2019


      If [End Date Field] = [linkedtable].[discretedatefield],  InsertDates column extends forward to the closest date in [linkedtable].[discretedatefield] that is  later than last date in raw result set.

      LastDate=19/02/2019   ,  [End Date Field] = "[Tab].[Dates]"

      [Tab].[Dates] = 13/02/2019, 20/02/2019, 27/02/2019, 06/03/2019...   

      InsertDates extends Forward to 27/02/2019


      [End Date Field] must refer to a DISCRETE DATE TIME FIELD in a table that is linked to the Multi-Function Profile dimension table. 


      Copy Mode

      Determines the values to use in rows added to the end of the raw result set.   Will be applied to all columns that exist at the point when the InsertDates process is run.

      Does not affect any rows added to the beginning of the raw result set.

      None / Repeat / EndStop

      • Default = None
      • None:   Inserted Rows will be blank
      • Repeat: Inserted Rows will repeat the last value in the raw result set.

      Setting [Copy Mode] = Repeat is useful in generating graphs where measures stay constant after a specific date, and so the last recorded value needs to be “carried forward.”

      EndStop:  {TODO}

      Required range

      Required.   Default = 0.

      If 0, the number of rows to be added during post-processing is determined by [Start of Month/Start Date Field] and [End of Month/end Date Field].

      If [Required Range] is less than the number of rows returned by other  parameter settings, then [Required Range] will remove rows from the end of the result set.

      If [Required Range] is greater than the number of rows returned by other parameter settings, then [Required Range] will extend the end of the result set until the specified number of rows is reached.

      If [Required Range] extends the result set, [Copy Mode] will be used to determine what to do for any existing columns.

      [Required Range] works in conjunction with [Interval] to determine the number of rows to add to the result set.

      Interval

      Required.

      Days / Weeks / Months

      Default = Days.

      The interval to use when calculating required number of rows from [Required Range].

      Not used if [Required Range] = 0

      • If [Interval] = Days, then [Required Range] refers to days and TotalRows = [Required Range]
      • If [Interval] = Weeks, then [Required Range] refers to weeks and TotalRows = [Required Range ] * 7
      • If [Interval] = Weeks, then [Required Range] refers to months and TotalRows = number of days in specified months.


      Examples

      Start Of Month

      If = Y, inserts records back to the start of the month of the first record in the record set.


      Start Date Field




      Required Range


      Basic Setup

      Without Insert Dates



      With Insert Dates








      Was this article helpful?
      What's Next