IfElse Calculated Field not Working


I am having difficulty with a QuickSight calculated field and am wondering if anyone can help me. This is the simple field I am trying to create: ifelse({Measure}>0,{Measure},"NA")

The error messaging is unhelpful and just says "The calculated field expression ifelse({Measure}>0,{Measure},"NA") is incorrect. Correct the syntax and choose Create again."

The only thing I can think of is perhaps the ifelse() function doesn't allow you to return a string, but I did not think that was the case per the docs.

Can anyone help me understand why this calculated field isn't working? Thanks!

asked 2 years ago4501 views
1 Answer

The Field you are trying to create is meant to be a numeric field but in the else branch you use a string.

To make your calculated field work , you need either to set a numeric value in the else or cast the measure to a string.

ifelse({Forecasted Monthly Revenue}>0,toString({Forecasted Monthly Revenue}),"NA")

But I do not really think you want that. Can you try to explain what you would like to achieve?

How would you show the field in a visual?

are you not interested in aggregating the value by dimensions?

Hope this helps,

answered 2 years ago
  • The measure I am trying to create is an estimate for projecting business performance for a future time period. Occasionally the estimate will be 0 or yield a div/0 error and in that case, I was hoping to simply have the KPI visual show "NA".

    Upon reading the docs more closely, I see that the then-expression and else-expression must have the same data type...but still I was hoping for some way to handle this situation where I show "NA" if the estimate is <0

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions