Use date range parameters to modify data retrieval period

How to use the expression evaluator

The expression evaluator is used to manipulate date ranges in URL data sources, making it possible to retrieve dynamic content more easily. By using the methods and shortcuts described in this article, you can retrieve almost any date range from data sources like Google Analytics, Facebook, or Twitter.

The date property uses the following format:

https://www.datasource.com/data?start-date={date.startOfQuarter}&end-date={date.today}

This will retrieve data from the start of the current quarter up to and including the current date. The expressions within the brackets can be modified using the following method and shortcuts shown below.

NOTE: these date expressions must be entered in the URL, not as a query parameter, during data source creation.

Data manipulation methods

These methods can be chained together at the beginning of an expression to refine the date expression. It is recommended that these methods are used in the order shown below. You can use a single method or multiple methods to refine your expression. Please note these methods are optional.

Recommended ordering:

  1. set(String) , set(String, String) [only one]
  2. tz(string), firstDOW(string), firstMOY(String) [any combination, only one of each]
  3. start(), end() [only one]
  4. add(int), addMinutes(int), addHours(int), addWeeks(int), addQuarters(int), addYears(int) [any cominbation]

Here's an example of how these will affect the URL: https://www.datasource.com/data?start-date={date.tz("est").start("00:00:00").startOfQuarter}&end-date={date.today}

set(dateString)

  • Set the working date in the "yyyy-MM-dd" format

set(dateString, formatString)

  • Set the working date in the given format

tz(tzCode)

  • Set the time zone

firstDOW(dayOfWeekAbbr)

  • Set the first day of the week
  • Affects the retrieval of weeks
  • Options: sun, mon, tue, wed, thu, fri, sat
  • Default: sun (Sunday)

firstMOY(monthAbbr)

  • Set the first month of the year
  • Affects the retrieval of quarters and years
  • Options: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
  • Default: jan (January)

start()

  • Set the current time to the start of the day, i.e. 00:00:00

end()

  • Set the current time to the end of the day, i.e. 23:59:59

add(nDays)

  • Adds N days to the current date
  • Can use negative {date.add(-60)} to go back in time
  • Does not return anything

addMinutes(nMinutes)

  • Adds N minutes to the current time
  • Can use negative {date.addMinutes(-30)} to go back in time
  • Does not return anything

addHours(nHours)

  • Adds N hours to the current time
  • Can use negative {date.addHours(-6)} to go back in time
  • Does not return anything

addWeeks(nWeeks)

  • Adds N weeks to the current date
  • Can use negative {date.addWeeks(-1)} to go back in time
  • Does not return anything

addMonths(nMonths)

  • Adds N months to the current date
  • Can use negative {date.addMonths(-6)} to go back in time
  • Does not return anything

addQuarters(nQuarters)

  • Adds N quarters to the current date
  • Can use negative {date.addQuarters(-2)} to go back in time
  • Does not return anything

addYears(nYears)

  • Adds N years to the current date
  • Can use negative {date.addYears(-1)} to go back in time
  • Does not return anything

Date Retrieval Shortcuts

These shortcuts should come after the date manipulation methods (see above). These shortcuts can either be the last element in the chain or followed by a date format method, which is the last element in the chain. Only one (or none) can be used in an expression. 

today

  • Returns the current date

yesterday

  • Returns the current date -1

last7Days

  • Returns the date 7 days ago

last30Days

  • Returns the date 30 days ago

last60Days

  • Returns the date 60 days ago

startOfWeek

  • Returns the start of the current week

Note: if firstDOW is set, weeks will be calculated using the given day.

endOfWeek

  • Returns the end of the current week

Note: if firstDOW is set, weeks will be calculated using the given day.

startOfMonth

  • Returns the start of the current month

endOfMonth

  • Returns the end of the current month

startOfQuarter

  • Returns the start of the current quarter

Note: if firstMOY is set, quarters will be calculated using the given month

endOfQuarter

  • Returns the end of the current quarter

Note: if firstMOY is set, quarters will be calculated using the given month

startOfYear

  • Returns the start of the current year

Note: if firstMOY is set, years will be calculated using the given month

endOfYear

  • Returns the end of the current year
Note: if firstMOY is set, years will be calculated using the given month

Date Format Methods

By default, Date Retrieval Shortcuts return dates in yyyy-MM-dd format. The Date Format methods are optional and if used, only one can be used in an expression and it must be the last element in expression chain. For additional information about date formats, refer to Oracle Simple Date Format.

format()

  • Returns the date in yyyy-MM-dd format.

format('epochTime')

  • Returns the date in epoch time (seconds) format. For example,
  • {date.format('epochTime')}
  • {date.yesterday.format('epochTime')}
  • {date.startOfQuarter.format('epochTime')}
Note that some APIs require an epoch time in milliseconds; in these cases, append "000" to the date string, for example, {date.add(-7).format('epochTime')}000.

format(formatString)

  • Returns the date in the format specified by formatString. For example,
  • {date.yesterday.format('yyyyMMdd')}

Examples

Commonly used date range parameters

Period

Start example

&

End example

Custom Range start-date=YYYY-MM-DD & end-date=YYYY-MM-DD
Today start-date={date.today} & end-date={date.today}
Last X Days start-date={date.last7Days}
start-date={date.last30Days}
start-date={date.last60Days}
&
&
&
end-date={date.last7Days}
end-date={date.last30Days}
end-date={date.last60Days}
Start/End of Period start-date={date.startOfWeek}
start-date={date.startOfMonth}
start-date={date.startOfQuarter}
start-date={date.startOfYear}
&
&
&
&
end-date={date.endOfWeek}
end-date={date.endOfMonth}
end-date={date.endOfQuarter}
end-date={date.endOfYear}

Custom date range query parameters

- You can use the negative to retrieve data sets from the past
- N may be any numerical values

Period

Start example

&

End example

Add N Minutes start-date={date.addMinutes(-30).format()} & end-date={date.addMinutes(-15).format()}
Add N Hours start-date={date.addHours(-24).format()} & end-date={date.addHours(-12).format()}
Add N Days start-date={date.add(-30).format()} & end-date={date.add(-30).format()}
Add N Weeks start-date={date.addWeeks(-52).format()} & end-date={date.addWeeks(-26).format()}
Add N Months start-date={date.addMonths(-5).format()} & end-date={date.addMonths(-1).format()}
Add N Quarters start-date={date.addQuarters(-3).format()} & end-date={date.addQuarters(-2).format()}
Add N Years start-date={date.addYears(-2).format()} & end-date={date.addYears(-1).format()}

Learn more

Use the data range query parameters with the following data sources:

Connecting to data sources

  1. Klipfolio Data Source Integrations
  2. Creating data sources
  3. Best practices for uploading your data
  4. Updating data sources
  5. About data source refresh
  6. Install the latest Google Analytics KlipStart Dashboard
  7. NEW: Install the latest Google AdWords KlipStart (for MCC accounts only)
  8. Guide to Web Accessible (REST API) data sources
  9. Authentication method: OAuth Token Authentication for Web Accessible Data Source
  10. Why does Klipfolio need specific permissions to connect my service accounts?
  11. Authentication method: 2 Step Authentication
  12. Authentication method: Basic HTTP Authentication
  13. Authentication method: API Key Authentication
  14. Managing OAuth tokens for connected accounts
  15. Use date range parameters to modify data retrieval period
  16. NEW: Signed Authentication Builder (Hash Helper) (advanced)
  17. Locate a data source ID
  18. SQL Databases
  19. Use files from your computer as a data source
  20. Use Email Attachments as a data source
  21. Use Excel spreadsheets as a data source
  22. NEW: Use Adobe Analytics as a data source
  23. NEW: Use Alexa as a data source
  24. Use Asana as a data source
  25. NEW: Use Atlassian JIRA as a data source
  26. NEW: Use AWeber as a Data Source
  27. NEW: Use Basecamp as a data source
  28. Use Bitly as a data source
  29. Use Box as a data source
  30. NEW: Use Buffer as a data source
  31. NEW: Use CallRail as a data source
  32. Use Campaign Monitor as a data source
  33. NEW: Use Chargify as a data source
  34. Use Cloud Monitor as a data source
  35. Use comScore Digital Analytix as a data source
  36. NEW: Use Desk.com as a data source
  37. Use Dropbox as a data source
  38. Use Eloqua as a data source
  39. Use Facebook as a data source
  40. NEW: Use Facebook Ads as a data source
  41. NEW: Use FluidSurveys as a data source
  42. Use Freshdesk as a data source
  43. NEW: Use Fusebill as a data source
  44. Use FTP or SFTP as a data source
  45. NEW: Use Glassdoor as a data source
  46. Use Google+ as a data source
  47. NEW: Use Google AdWords as a data source
  48. Use Google Analytics as a data source
  49. Use Google Analytics to create a Multi-Channel Funnel (MCF) API data source
  50. NEW: Use Google Calendar as a data source
  51. Use Google Drive Apps as a data source
  52. Use Google Webmaster Tools as a data source
  53. NEW: Use Harvest as a Data Source
  54. NEW: Use GoSquared as a data source
  55. Use Heartbeat as a Data Source
  56. NEW: Use HubSpot as a data source
  57. NEW: Use iFormBuilder as a data source
  58. PREVIOUS: Use iFormBuilder as a data source
  59. Use Infusionsoft as a data source
  60. NEW: Use Insightly as a data source
  61. NEW: Use Instagram as a data source
  62. NEW: Use Intercom as a data source
  63. Use Intervals as a data source
  64. NEW: Use Lighthouse as a Data Source
  65. Use LinkedIn as a data source
  66. Use Localytics as a data source
  67. Use MailChimp as a data source
  68. NEW: Use Mixpanel as a data source
  69. NEW: Use Marketo as a data source
  70. NEW: Use Moz as a data source
  71. NEW: Use New Relic as a data source
  72. Use Nicereply as a data source
  73. Use Optimizely as a data source
  74. NEW: Use Pardot as a data source
  75. Use Pingdom as a data source
  76. NEW: Use Pipedrive as a data source
  77. Use Podio as a data source
  78. NEW: Use QuickBooks as a data source
  79. Use QuickBooks with the Email Attachment Connector
  80. NEW: Use Quintly as a data source
  81. Use Radian6 as a data source
  82. NEW: Use RSS news feeds as a data source
  83. Use Salesforce as a data source
  84. Use Searchmetrics as a data source
  85. Use Sendible as a data source
  86. NEW: Use Shopify as a data source
  87. Use ShoreTelSky as a data source
  88. NEW: Use Slack as a data source
  89. Use Smartsheet as a data source
  90. NEW: Use Stripe as a data source
  91. Use SurveyGizmo as a data source
  92. NEW: Use Swrve as a data source
  93. Use Toggl as a data source
  94. NEW: Use Trello as a data source
  95. NEW: Use Taleo as a data source
  96. NEW: Use Talkwalker as a data source
  97. NEW: Use Todoist as a data source
  98. Use Topsy as a data source
  99. Use Twitter as a data source
  100. Use UserVoice as a data source
  101. NEW: Use VKontakte as a data source
  102. Use Webtrends as a data source
  103. NEW: Use Xero as a data source
  104. Use XMLA as a data source
  105. NEW: Use Yahoo Finance as a data source
  106. Use YouTube Analytics as a data source
  107. Use Zendesk as a data source
  108. Use Zoho CRM as a data source
  109. Automate data upload using the data source API
  110. Use Unbounce as a data source
  111. Data examples

Feedback and Knowledge Base