Loading

Creating Date Calculations for Specific Date Ranges

Publiceringsdatum: Apr 13, 2024
Uppgift
How to create calculated fields that can be used to filter out specific date ranges, such as the last n number of days, the previous week, or a specific range of dates.
Steg
Use the following formulas as templates from which to create calculated fields. In the formulas below, Date is the name of the date dimension.

Filtering a Range of Days

Use the following formula to create a calculated filed that filters for the last n days:

IF [Date] < (today() - <n> + 1) OR [Date] > today() THEN NULL ELSE [Date] END
For example, a calculated field that filters data for the last 90 days would have the following formula: 
IF [Date] < (today() - 90 + 1) OR [Date] > today() THEN NULL ELSE [Date] END
Formula for the last 7 days: 
IF [Date] < (today() - 7 + 1) OR [Date] > today() THEN NULL ELSE [Date] END
 

Filtering Weeks

Use the following formula to filter data for the week before last: 
IF [Date] > (today() - 8 + 1) OR [Date] <= (today() - 15 + 1)
THEN NULL ELSE [Date] END
Use the following formula to filter for the last eight weeks: 
IF 
DATEPART('week', [Date]) <DATEPART('week', TODAY()) - 8 
OR 
DATEPART('week', [Date]) > DATEPART('week', TODAY()) -1 
THEN NULL 
ELSE [Date]
END
Ytterligare resurser
  • You can use a date parameter, another field, or a fixed date in place of TODAY() in order to base the calculation on a different anchor date.
  • The calculations can be modified with any of the date_part values listed in Date Functions to filter different date levels.

To view the above examples in action, see the video below.

Note: the Sample - Superstore data source's maximum date is January 6, 2016 for for the Shipping date dimension. This means that there we are not able to use TODAY() but we use a substitute. 

Knowledge-artikelnummer

001458132

 
Laddar
Salesforce Help | Article