CODEWITHSUNDEEP
amazon-quicksight
Tony
Tony

Reputation: 9571

Quicksight parse date into month

Maybe I missed it but I'm attempting to create a dynamic 'Month' parameter based on a datetime field - but can't seem to get just the month! ? Am I missing something ?

here's my source DTTM date/time field - enter image description here

Upvotes: 2

Views: 13469

Answers (5)

Gabriele Mariotti
Gabriele Mariotti

Reputation: 363677

You can add a calculated field using the extract function:

extract returns a specified portion of a date value. Requesting a time-related portion of a date that doesn't contain time information returns 0.

extract('MM', date_field)

Upvotes: 0

mpal09
mpal09

Reputation: 1

You can use the extract function. Works like this:

event_timestamp Nov 9, 2021

extract('MM', event_timestamp)
11

Upvotes: 0

DataNut
DataNut

Reputation: 317

You'll need to hardcode the desired results using ifelse, min, and extract. Extract will pull out the month as an integer. Quicksight has a desire to beginning summing integers, so we'll put MIN in place to prevent that.

ifelse(min(extract('MM',Date)) = 1,'January',min(extract('MM',Date)) = 2,'February',min(extract('MM',Date)) = 3,'March',min(extract('MM',Date)) = 4,'April',min(extract('MM',Date)) = 5,'May',min(extract('MM',Date)) = 6,'June',min(extract('MM',Date)) = 7,'July',min(extract('MM',Date)) = 8,'August',min(extract('MM',Date)) = 9,'September',min(extract('MM',Date)) = 10,'October',min(extract('MM',Date)) = 11,'November',min(extract('MM',Date)) = 12,'December','Error')

Also, I apologize if this misses the mark. I'm not able to see the screeshot you posted due to security controls here at the office.

Upvotes: 0

kli
kli

Reputation: 491

In Manage Data > Edit [selected] Data Set > Data source

Just add 'calculated field':

truncDate('MM', date)

where MM returns the month portion of the date.

See manual of truncDate function

Upvotes: 2

skabo
skabo

Reputation: 86

The only place in Quicksight that you can get just a month, e.g. "September" is on a date-based axis of a visual. To do so, click the dropdown arrow next to the field name in the fields list, select "Format: (date)" then "More Formatting Options..." then "Custom" and enter MMMM in the Custom format input box. Quicksight menu selection as described

This will then show the full month name on the date axis in your visual. NB It will use the full month name on this visual for ALL time period "Aggregations" - e.g. if you change the visual to aggregate by Quarter, it will display the full name of the quarter's first month etc.

If you are talking about "Parameters" in the Quicksight analysis view then you can only create a "Datetime" formatted parameter and then only use the "Date picker" box format for this parameter in a control (+ filter).

If you use a calculated field in either data preparation or analysis view the only date functions do not allow full month names as an output, you can get the month number as an integer or one of the allowed date formats here: https://docs.aws.amazon.com/quicksight/latest/user/data-source-limits.html#supported-date-formats

Upvotes: 0

Related Questions