Calculated Field Shows In Preview but Fail Refresh

[sebatota]

I have a dataset that is reading from a Redshift table. One of the columns I read in is start_date, which is properly being identified as a Date and working completely fine within our dashboard.

I now want to add a new calculated field off of that start_date column as follows:

ifelse(
formatDate({start_date}, 'MMM dd, yyyy') = 'Dec 11, 2022', 
'Baseline Week', 
formatDate({start_date}, 'MMM dd, yyyy')
)

When I add this new column, I can see in the dataset preview that the column is being properly set, but when a refresh is triggered (with no new data being added to the dataset), the refresh fails.

I even tried simplifying the column to a String column of:

formatDate({start_date}, 'MMM dd, yyyy')

and even that fails on refresh, but doesn’t fail in the preview.

This is the error I see when a refresh fails:

Error type:
INVALID_DATE_FORMAT Learn more
Your function expression contains an unsupported date. Correct the date format and try again.

Error details:
Format MMM dd, yyyy is not supported.

I figured that for some reason MMM dd, yyyy may not be supported, so I tried to use formatDate(orderDate, 'dd MMM yyyy') per the example in the documentation, but still saw the same issue.

Can someone help me understand why this could be happening?

[ErikG]

Hi @sebatota
maybe you can do it the other way around as start date is already a date. By parse the “Dec 11, 2022” into a date.

parseDate - Amazon QuickSight

BR

[sebatota]

I need the resulting column to be a String since sometimes it will be a formatted date, while other times it will simply be a predetermined string.

Are you saying I should convert the date to a string and back to a date?

[ErikG]

Not exactly. Just in the if-expression.

ifelse({start_date} = parseDate(‘Dec 11, 2022’, MMM dd, yyyy),
‘Baseline Week’,
formatDate({start_date}, ‘MMM dd, yyyy’)
)

[sebatota]

I still see the exact same error when refreshing.

[ErikG]

Could you try a supported format listed in parseDate for Redshift?

[sebatota]

Quick question for you, when I use formatDate({start_date}, 'MMM dd, yyyy') for the calculated field, what is start_date referring to? The already imported start_date Date column in the SPICE dataset, or the raw data from Redshift?

My assumption here is that it should use the already imported and formatted Date object in the SPICE dataset, and not the Redshift raw data.

[sinjulie0]

It seems like the issue is related to the date format used in the calculated field. The error message indicates that the format ‘MMM dd, yyyy’ is not supported during the refresh, even though it works in the preview.

To address this, consider using a date format that is compatible with the underlying data source. Redshift might have specific requirements for date formats. You can try using a more standard format, such as ‘YYYY-MM-DD’, in your calculated field:

ifelse(
formatDate({start_date}, ‘YYYY-MM-DD’) = ‘2022-12-11’,
‘Baseline Week’,
formatDate({start_date}, ‘YYYY-MM-DD’)
)

If this doesn’t resolve the issue, you may need to check the Redshift documentation or contact your data team to ensure that the date format used in your calculated field aligns with Redshift’s expectations during the refresh process.

Thanks,
UPSers

[sebatota]

I’m still not following why the Redshift data field format would matter. Shouldn’t the {start_date} field in the computed field be using the date object from the SPICE dataset? formatDate({start_date}, ‘YYYY-MM-DD’) should be formatting an existing Date object to a string, and not the other way around, so I’m not quite sure why this matters.

To be clear, YYYY-MM-DD is not the format my date is stored as in Redshift, it’s what I want the output string to look like after parsing the Date object.

[DylanM]

Hello @sebatota, since this calculation is occurring at the dataset level, it is referencing the Redshift data on refresh. In that case, you would want to use the parseDate guide from QuickSight to make sure you are referencing your start_date field related to a date format accepted by Redshift. That will ensure no errors are thrown on refresh.

An alternative option would be to create the calculated field at the analysis level. That way it would only ever reference the data after it is loaded into SPICE and would have no impact on the refresh.

I will mark my response as the solution, but if you have any further questions, please let me know!

[sinjulie0]

Hello,
I am still searching for cause which is creating this problem but it really hard to understand the situation.
Joinpd helped me resolving the issue.