API - Visual ConditionalFormatting validation fails when creating template

Question & Answer
,
1

Hello QuickSight Community,

I’ve downloaded a template from a QuickSight account using describe-template-definition and I want to use that to re-create it in another account. One of the issues I’m encountering is that some of the ConditionalFormattingOptions on Visuals fail because the expression is invalid.

There are two different cases in which this happens:

  1. The expression contains a logical operator like AND:
{
    "ConditionalFormattingOptions": [
        {
            "PrimaryValue": {
                "TextColor": {
                    "Solid": {
                        "Expression": "AVG({latest_proficiency}) < 50",
                        "Color": "#DE3B00"
                    }
                }
            }
        },
        {
            "PrimaryValue": {
                "TextColor": {
                    "Solid": {
                        "Expression": "(AVG({latest_proficiency}) >= 50) AND (AVG({latest_proficiency}) <= 74)",
                        "Color": "#FF8700"
                    }
                }
            }
        },
        {
            "PrimaryValue": {
                "TextColor": {
                    "Solid": {
                        "Expression": "AVG({latest_proficiency}) >= 75",
                        "Color": "#2CAD00"
                    }
                }
            }
        }
    ]
}

In the UI, the conditional formatting for an analysis created from this template (successfully) shows up as this:

image
image251×718 24.1 KB

  1. The expression contains a calculated field with a custom aggregation formula:
{
    "ConditionalFormattingOptions": [
        {
            "PrimaryValue": {
                "TextColor": {
                    "Solid": {
                        "Expression": "{Proficiency Improvement %} <= 0",
                        "Color": "#DE3B00"
                    }
                }
            }
        },
        {
            "PrimaryValue": {
                "TextColor": {
                    "Solid": {
                        "Expression": "{Proficiency Improvement %} > 0",
                        "Color": "#2CAD00"
                    }
                }
            }
        }
    ]
}

The errors I’m seeing are similar to this one:

Error while parsing conditional formatting expression: {Proficiency Improvement %} <= 0. Error : Invalid conditional formatting expression. 

Following are the valid expression formats for different comparison methods when value field and target value field are defined. 

DIFFERENCE comparison method: SUM({ValueField})-COUNT({TargetValueField}) >= 100, 
PERCENT comparison method: SUM({ValueField})/nullIf(COUNT({TargetValueField}),0) < 100, 
PERCENT_DIFFERENCE comparison method: (SUM({ValueField})/nullIf(COUNT({TargetValueField}),0))-1 = 100. 

Following are the valid expression formats for different comparison methods when value field and trend group field are defined.

When trend group field is other than date and time type field, 
DIFFERENCE comparison method: difference(SUM({ValueField}),[SUM({ValueField}) DESC],1,[]) = 100, 
PERCENT comparison method: percentDifference(SUM({ValueField}),[SUM({ValueField}) DESC],1,[])+1 <= 100, 
PERCENT_DIFFERENCE comparison method: percentDifference(SUM({ValueField}),[SUM({ValueField}) DESC],1,[]) > 100. 

When trend group field is date and time type field, 
DIFFERENCE comparison method: difference(SUM({ValueField}),[{TrendGroupField} DESC],1,[]) = 100, 
PERCENT comparison method: percentDifference(SUM({ValueField}),[{TrendGroupField} DESC],1,[])+1 <= 100, 
PERCENT_DIFFERENCE comparison method: percentDifference(SUM({ValueField}),[{TrendGroupField} DESC],1,[]) > 100. 

Aggregation functions and comparison operator can be any of the accepted functions or operator respectively.

The problem is that describe-template-definition return a JSON object that is not accepted by create-template. The template works fine when creating analyses/dashboards from it.

Thanks,
Adrian

2

Hi @Adrian_Vacaru

For your second use case have you tried writing out the calculated field instead of referencing it?

However, besides that it seems like an error.

I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf.

1 Like
3

Hi @Adrian_Vacaru ,

Revising my response for clarity.
The conditional formatting rules that you have in the attached image shouldn’t have created an entry with the AND operator. That should happen only when you use between option.
Please create the support case as Max suggested to see how your current analysis ended up having such a definition.

Also, you can try creating a copy of the analysis, reapplying the conditional formatting and extracting the definition from the new analysis. You should be able to use the new definition for both scenarios.

Regards,
Arun Santhosh