Learn about date formulas.

Date functions are useful when you want to compare data collected between two date periods. Date formulas allow you to apply date related functions to your formulas.

Date formulas

The date formulas include:

Function Description Examples
add_days Returns the result of adding the specified number of days to the given date. add_days (01/30/2015, 5) = 02/04/2015
add_days (invoiced, 30)
add_minutes Returns the result of adding the specified number of minutes to the given date/datetime/time. add_minutes ( 01/30/2015 00:10:20 , 5 ) = 01/30/2015 00:15:20
add_minutes ( invoiced , 30 )
add_months Returns the result of adding the specified number of months to the given date. add_months ( 01/30/2015, 5 ) = 06/30/2015
add_months ( invoiced_date , 5 )
add_seconds Returns the result of adding the specified number of seconds to the given date/ datetime/ time. add_seconds ( 01/30/2015 00:00:00, 5 ) = 06/30/2015 00:00:05
add_seconds ( invoiced_date , 5 )
add_weeks Returns the result of adding the specified number of weeks to the given date. add_weeks ( 01/30/2015, 2 ) = 02/13/2015
add_weeks ( invoiced_date , 2 )
add_years Returns the result of adding the specified number of years to the given date. add_years ( 01/30/2015, 5 ) = 01/30/2020
add_years ( invoiced_date , 5 )
date Returns the date portion of a given date. date (home visit)
day Returns the number (1-31) of the day of the month for the given date. day (01/15/2014) = 15
day (date ordered)
day_number_of_quarter Returns the number of the day in a quarter for a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. day_number_of_quarter (01/30/2015) = 30

In the following example, May 1st is the start of the fiscal year.

day_number_of_quarter (01/30/2015, 'fiscal') = 91
day_number_of_week Returns the number (1-7) of the day in a week for a given date. Monday is 1, and Sunday is 7. day_number_of_week(01/15/2014) = 3
day_number_of_week (shipped)
day_number_of_year Returns the number (1-366) of the day in a year from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. day_number_of_year (01/30/2015) = 30

In the following example, May 1st is the start of the fiscal year.

day_number_of_year ( 01/30/2015, 'fiscal' ) = 275
day_number_of_year (invoiced)
day_of_week Returns the day of the week for the given date. day_of week (01/30/2015) = Friday
day_of_week (serviced)
diff_days Subtracts the second date from the first date and returns the result in number of days, rounded down if not exact. diff_days (01/15/2014, 01/17/2014) = -2
diff_days (purchased, shipped)
diff_hours Subtracts the hour of the second date from the hour of the first date and returns the result in number of hours. diff_hours (01/15/2014 01:59:59, 01/15/2014 02:00:00) = -1
diff_hours (01/15/2014 01:00:00, 01/15/2014 01:59:59) = 0
diff_hours (clicked, submitted)
diff_minutes Subtracts the minute of the second date from the minute of the first date and returns the result in number of minutes. diff_minutes (01/15/2014 01:59:59, 01/15/2014 02:00:00) = -1
diff_minutes (01/15/2014 01:00:00, 01/15/2014 01:00:59) = 0
diff_minutes (clicked, submitted)
diff_months Subtracts the month of the second date from the month of the first date and returns the result in number of months.

Use the optional third parameter to specify if ThoughtSpot should use a custom calendar to calculate the result.		 diff_months (12/25/2013, 01/01/2014) = -1
diff_months (01/01/2014, 01/25/2014) = 0
diff_months (purchased, shipped)
diff_months (purchased, shipped, fiscal)
diff_quarters Subtracts the quarter of the second date from the quarter of the first date and returns the result in number of quarters.

Use the optional third parameter to specify if ThoughtSpot should use a custom calendar to calculate the result.		 diff_quarters (12/31/2013, 01/01/2014) = -1
diff_quarters (01/01/2014, 03/31/2014) = 0
diff_quarters (purchased, shipped)
diff_quarters (purchased, shipped, fiscal)
diff_time Subtracts the second date from the first date and returns the result in number of seconds. diff_time (01/30/2014, 01/31/2014) = -86,400
diff_time (clicked, submitted)
diff_weeks Subtracts the week of the second date from the week of the first date and returns the result in number of weeks.

Use the optional third parameter to specify if ThoughtSpot should use a custom calendar to calculate the result.		 diff_weeks (01/05/2014, 01/06/2014) = -1
diff_weeks (01/06/2014, 01/12/2014) = 0
diff_weeks (purchased, shipped)
diff_weeks (purchased, shipped, fiscal)
diff_years Subtracts the second date from the first date and returns the result in number of years.

Use the optional third parameter to specify if ThoughtSpot should use a custom calendar to calculate the result.		 diff_years (12/25/2013, 01/01/2014) = -1
diff_years (01/01/2014, 12/25/2014) = 0
diff_years (purchased, shipped)
diff_years (purchased, shipped, fiscal)
hour_of_day Returns the hour of the day for the given date. hour_of_day (received)
is_weekend Returns true if the given date falls on a Saturday or Sunday. is_weekend (01/31/2015) = true
is_weekend (emailed)
month Returns the month from the given date. month (01/15/2014) = January
month (date ordered)
month_number Returns the number (1-12) of the month from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. month_number (09/20/2014) = 9

In the following example, May 1st is the start of the fiscal year.

month_number ( 09/20/2014, 'fiscal' ) = 5
month_number (purchased)
month_number_of_quarter Returns the month (1-3) number for the given date in a quarter. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. month_number_of_quarter (02/20/2018) = 2

In the following example, May 1st is the start of the fiscal year.

month_number_of_quarter (02/20/2018,'fiscal' ) = 1
now Returns the current timestamp. now ()
quarter_number Returns the number (1-4) of the quarter associated with the given date. You can add an optional second parameter to specify 'fiscal' or 'calendar' dates. The default is 'calendar'. quarter_number ( 04/14/2014) = 2

In the following example, May 1st is the start of the fiscal year.

quarter_number ( 04/14/2014, 'fiscal' ) = 4
quarter_number ( shipped )
start_of_month Returns MMM yyyy for the first day of the month. Your installation configuration can override this setting so that it returns a different format such as MM/dd/yyyy. Speak with your ThoughtSpot administrator for information on doing this. start_of_month ( 01/31/2015 ) = Jan FY 2015
start_of_month (shipped)
start_of_quarter Returns the date for the first day of the quarter for the given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. start_of_quarter ( 04/30/2014) = Apr 2014

In the following example, May 1st is the start of the fiscal year.

start_of_quarter ( 04/30/2014, 'fiscal') = Feb 2014
start_of_quarter (sold)
start_of_week Returns the date for the first day of the week for the given date. start_of_week ( 01/31/2020 ) = 01/27/2020
start_of_week (emailed)
start_of_year Returns the date for the first day of the year for the given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. start_of_year (04/30/2014) returns Jan 2014

In the following example, May 1st is the start of the fiscal year.

start_of_year (04/30/2014, 'fiscal') returns May 2013
start_of_year (joined)
time Returns the time portion of a given date. time (1/31/2002 10:32) = 10:32
time (call began)
today Returns the current date. today ()
week_number_of_month Returns the week number for the given date in a month. week_number_of_month(03/23/2017) = 3
week_number_of_quarter Returns the week number for the given date in a quarter. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. week_number_of_quarter (01/31/2020) = 5

In the following example, May 1st is the start of the fiscal year.

week_number_of_quarter (05/31/2020, 'fiscal') = 5
week_number_of_year Returns the week number for the given date in a year. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. week_number_of_year (01/17/2014) = 3

In the following example, May 1st is the start of the fiscal year.

week_number_of_year ( 01/17/2014, 'fiscal') = 38
year Returns the year from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. year (01/15/2014) = 2014

In the following example, May 1st is the start of the fiscal year. Per standard convention, the fiscal year is defined by the year-end date.

year (12/15/2013, 'fiscal' ) = 2014
year (date ordered)

Calculate date formulas

Calculating date formulas is useful when you want to compare data from different date periods. Here are some examples of using date formulas:

Example 1

The following example shows you how to create formulas that you can use to compare data from this week to last week.

  • The formula for this week is: week ( today () ) - week (date)
  • The formula for last week is: diff_days ( week ( today () ) ) , week ( date ) )

Example 2

The following example shows you how to calculate the percent increase from the last date period to this period in terms of revenue.

  1. Create the formula: this week revenue = sum ( if ( this week ) then revenue else 0 )
  2. Then create the formula: last week revenue = sum ( if (last week ) then revenue else 0 )
  3. Use nested formulas to calculate the percent increase by creating a parent formula: percent increase = ( ( this week revenue - last week revenue) / last week revenue ) * 100

Fiscal and Gregorian calendars

For the following date formulas, you can further specify either fiscal or Gregorian calendar on which to base date calculations. (If you do not specify a calendar type, the formula defaults to standard Gregorian, with the year starting in January.)

  • day_number_of_quarter
  • day_number_of_year
  • month_number
  • month_number_of_quarter
  • quarter_number
  • start_of_quarter
  • start_of_year
  • week_number_of_quarter
  • week_number_of_year
  • year

Your ThoughtSpot administrator and ThoughtSpot Support can create a custom calendar to start on any month. If the fiscal year is not explicitly configured in the system, fiscal defaults to January, the same as the Gregorian calendar.

For example, the formula month_number_of_quarter (05/31/2014) would return 2 based on the default Gregorian calendar, whereas the formula month_number_of_quarter (05/31/2014, 'fiscal') would return 1 if your administrator has configured the fiscal calendar to start in May.