Filtres relatifs sur les dates

Under filter, select the comparison (less than, greater than), than instead of choosing a value, select « More Options » and « SQL query », and use the following:

• timestampadd(SQL_TSI_DAY, {x} ,CURRENT_DATE)where {x} is the number of days forward or back (and usually you’ll want back, so a negative number) number of days. This sql can be used for months or years as well, by changing the SQL_TSI constant accordingly.
  • Today:
  • CURRENT_DATECURRENT_TIMESTAMP
  • Setting Data Type:
  • CAST(“dimension.field” AS X)X can be DATE or TIMESTAMP if a date field.X can be STRING or INTEGER if a number.
  • Setting Time Part:
  • X(“dimension.field”)X can equal YEAR, QUARTER, MONTH, DAY, DAYOFYEAR, DAYOFWEEK, HOUR, MINUTE, or SECOND for any date field.
  • Filter for 7 Days Ago:
  • TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)
  • First Day of Last Month:
  • TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
  • Last Day of Last Month:
  • TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
  • First Day of Current Year:
  • TIMESTAMPADD(SQL_TSI_YEAR, 0, TIMESTAMPADD( SQL_TSI_DAY , DAYOFYEAR( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
  • All Transactions in last 14 days:
  • « Transaction Date ». »Transaction Date »>= TIMESTAMPADD( SQL_TSI_DAY, -14, CURRENT_DATE))
  • All Transactions in last 2 years:
  • « Transaction Date ». »Transaction Date »>= TIMESTAMPADD( SQL_TSI_YEAR, -2, CURRENT_DATE))
  • All Transactions in last 3 months:
  • « Transaction Date ». »Transaction Date »>= TIMESTAMPADD( SQL_TSI_MONTH, -3, CURRENT_DATE))
    • All Transactions in last 1 week:
  • « Transaction Date ». »Transaction Date »>= TIMESTAMPADD( SQL_TSI_WEEK, -1, CURRENT_DATE))

How to use the TIMESTAMPADD parameter to retrieve by today – X time in an Alma Analytics report