Calculated Field Shows in Preview But Fails During Dataset Refresh

Issue Description
I am creating a calculated field for reporting year while the dataset creation process, and the formula is formatDate(month_start, 'YYYY'). While this works when I preview the dataset (no errors while creating the calculated field) but it fails to refresh when I click on ‘Save & Publish’ with the message saying Format YYYY is not supported.

Detailed Error Message

Error type: INVALID_DATE_FORMAT [Learn More]
(https://docs.aws.amazon.com/console/quicksight/errors)
Your function expression contains an unsupported date. 
Correct the date format and try again.

Error details: Format YYYY is not supported.

Expected Behavior/ Help Needed
If it is invalid format then it should not work while creating the calculated field within the dataset nor it should work while creating the same field within the analysis. On the other hand, if it is correct format then it should work with the dataset creation (and should refresh the data as expected). Either of those two should happen, and that is why I believe it is a bug and should be fixed.

Current Workaround
I created the calculated field (with same formula as above) within the analysis and then I was able to achieve same output but it is not ideal as I want to pre-calculate as many calculated fields as possible.

Hi @bhaumikn,
When trying to work with date formatting in QuickSight, Year is expressed through lowercase y’s.
What if you try formatDate({month_start}, 'yyyy')

Let me know if this works for your case

1 Like