Error in AWS QuickSight Dashboard Creation - CONTEXTUAL_UNKNOWN_SYMBOL for Calculated Field

Question & Answer
,
1

I’m reaching out regarding an error encountered while using the aws quicksight create-dashboard command. The error message received was:

        "Errors": [
            {
                "Type": "INVALID_CALCULATED_COLUMN_EXPRESSION",
                "Message": "Calculated field calc_ER/1000_Spec failed with error code CONTEXTUAL_UNKNOWN_SYMBOL during validation",
                "ViolatedEntities": [
                    {
                        "Path": "calculated-field/calc_ER/1000_Spec"
                    }
                ]
            }
        ],
        "VersionNumber": 1,
        "Status": "CREATION_FAILED",

I’ve verified the calculated column in the JSON file, and all elements seem to be in the correct order. This is unexpected since the same configuration worked fine previously.

Below are the relevant calculated fields from the JSON:

{
“DataSetIdentifier”: “ds_claims_provider_metric”,
“Name”: “calc_ER/1000”,
“Expression”: “({r1000_x_factor_config} * ervisits)/(provpatients*{reporting_period_config})”
},
{
“DataSetIdentifier”: “ds_claims_provider_metric”,
“Name”: “calc_ER/1000_Spec”,
“Expression”: “avgOver({calc_ER/1000},[{PCP PrimaryCare Specialty}], PRE_FILTER)”
}

I reviewed similar issues on the portal, and one suggestion was to move the calculated column to the Dataset level. However, this solution could require significant changes, which I’d prefer to avoid if possible. Do you have any alternative recommendations or insights into the root cause of this issue?

Thank you for your help.

2

Hey!

The error you are getting means that there are some issues with Quicksight recognizing a part of the expression, because it is referring to another calculated field. Before moving to the dataset level there are a few things that you can try:

  • I would suggest changing the name to not use the / this can cause parsing error on the Quicksight end
  • If PCP PrimaryCare Specialty is field with spaces, try putting it in double quotes "PCP PrimaryCare Specialty"
  • Try putting this all in one calculated field and slowly adding complexity and layers

I assume there is no issues with any hidden characters or formatting issues with the calculated fields, but it is worth to check for that as well! Hopefully you are able to resolve your issue!

3

Thank you for your response.

I have used “/” in many calculated column names. Just so its clear, the same JSON files were working fine before. I haven’t changed anything on the calculated fields in question now. I am not sure if anything changed on the Quicksight API, which is causing an issue.

As for consolidating everything into a single calculated field: doing so would reduce reusability and make future maintenance more difficult.

4

Hi @rajesh.sebastian,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

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

Thank you!