Amazon QuickSight – truncDate
The ‘truncDate’ function in Amazon QuickSight is used to truncate a date to a specific period, such as year, quarter, month, week, or day.
Syntax
#Start# truncDate('period', date) #End#
This function takes two arguments:
- ‘period’: This parameter specifies the period to which the date is to be truncated. It can take one of the following values: ‘YYYY’ (year), ‘YYYY-Qn’ (quarter), ‘YYYY-MM’ (month), ‘YYYY-WW’ (week), or ‘YYYY-MM-DD’ (day).
- date: This is the date value that is to be truncated.
Suppose we have a dataset that contains information about sales transactions and their dates. We can use the truncDate() function in Amazon QuickSight to truncate the dates to the nearest month.
To truncate the dates to the nearest month, we can create a new calculated field in Amazon QuickSight with the following expression:
Example
#Start# truncDate('YYYY-MM', sales_date) #End#
This expression will truncate the dates in our dataset to the nearest month.
For instance, if our sales transaction data is as follows:
| Transaction ID | Sales Date | Sales Amount |
| 1 | 2022-02-15 | $100 |
| 2 | 2022-03-21 | $200 |
| 3 | 2022-04-02 | $300 |
| 4 | 2022-05-19 | $400 |
| 5 | 2022-06-11 | $500 |
Then, the sales transaction data with dates truncated to the nearest month will be:
| Transaction ID | Sales Date | Truncated Sales Date | Sales Amount |
| 1 | 2022-02-15 | 2022-02-01 | $100 |
| 2 | 2022-03-21 | 2022-03-01 | $200 |
| 3 | 2022-04-02 | 2022-04-01 | $300 |
| 4 | 2022-05-19 | 2022-05-01 | $400 |
| 5 | 2022-06-11 | 2022-06-01 | $500 |
In this example, we can see that the truncDate() function has truncated the dates to the nearest month, resulting in a cleaner and more consistent dataset.