Calculated Field Shows in Preview But Fails During Dataset Refresh

Question & Answer
, ,
1

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.

2

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
3

Hi @bhaumikn,
It’s been awhile since we last heard from you on this thread, did you have any additional questions regarding your post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

4

The lowercase would work but the expected behavior is that preview should fail if the the format is not correct but it does work in the preview.

In fact, it shouldn’t allow the creation of the field if the formula is incorrect - that, to me, is a bug and should be addressed.

5

Hi @bhaumikn,
I’ll mark this as a feature request so the support team to see and make adjustments to this bug.

Let us know if you have any additional questions or comments to add; if we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

6

Hi @bhaumikn,
Since I’ve marked this as a feature request and we have not heard back any further, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!