Use Case

 A new advanced function has been added to return the days between the selected date fields in report tables.

Days between date with offset function returns the days between the selected field's date (the end date) and another column's date (the start date) with an offset (eg. End Date +/- Offset - Start Date). 

Formula Used: Col 2 - Col 1 +/- (Value) offset 

For example: Month End Date - Month Start Date +/- (Value) offset

Follow the example below to calculate days between dates with offset, using the data set. 

  1. Login as an authorized user.
  2. Click on the Plus sign that appears at the top right corner and select Report.
  3. On the New Report window, click on a metric view that will form the basis of your analysis. Here, for instance, Subject Visits is selected. 
  4. On the report interface, add your required data fields. Ensure that you have a dimension and 2 related date fields.
  5. To drag a data field in the Columns box, expand one of the folders that appear in the left pane of the report interface window and by using the click-and-drag method on the columns, populate the Columns box. You can use the search box present above the folders to search for a specific column. Here, for instance, Site ID, Month Start Date - Site Activation Date, Month End Date - Site Activation Date are selected.
  6. On the Advanced Function tab, select Date & Time in the Select Function drop-down box.
  7. Select Days between Date with Offset in the succeeding drop-down box. You can also search for the function by entering text in the search box that appears above the drop-down box. Provide the following inputs:
    1. Start Date: Select the column to compare dates from.
    2. Offset: Enter the number of days that can be added or subtracted from the selected field.  xample, End Date +/- Offset.
    3. Absolute: Select Yes to show the absolute difference between the dates else select No.
  8. Click Save to save the changes. The resultant report is shown below.