Create visual to display text value from calculated field

Question & Answer
, ,
1

Hi all,

I have defined two parameters, Aircraft and Check Availability Date so a user can enter values to check if an aircraft is available or not. Based on these parameters, a calculated field (aircraftAvailability) is defined that returns either “Available” or “Unavailable”. Although the calculated field is correctly displayed in the report, when I try to create a visual to display it in a more fancy and user friendly way, I get an error no matter which visual I choose. Isn’t there a way to present the result of the calculated field in a more fancy way than the insertion in the report?

Screenshot from 2023-11-28 15-02-16
Screenshot from 2023-11-28 15-02-161530×375 50.2 KB

My calculated field is defined like this:

ifelse(
    sum(
        ifelse(
            {Aircraft Registration} = ${aircraftRegistration} AND 
            {Start date} <= ${checkDate} AND 
            {End date} >= ${checkDate},
            1,
            0
        )
    ) > 0,
    'Unavailable',
    'Available'
)
2

Hi @Fotis_flex
did you tried to break down the different functions to see which one is related to the error?
I guess it is the nested sum in the ifelse.
BR

1 Like
3

@ErikG I don’t think there is an error in my calculated field. It is produced successfully and as you can see in first pic above, I can include it in the table (aircraftAvailability) and display its value. I just can’t visualize it in a separate visual.

4

sure syntax is right but if you add function by function at what point is the visual breaking down?

5

@ErikG Yes I get you. The visual breaks down when the ifelse function is applied. That is because the field is converted from numeric to string. I can create a visual “Gauge chart” displaying green color when sum=0/Available and red color when sum>1 but I would also like to implement this pattern also with the text labels (available/unavailable). I just realize that there isn’t any QS visual type to include such condition with string fields. Do you think there is a work-around?

1 Like
6

Hello @Fotis_flex, I am a curious about the type of visual you are trying to utilize to handle this display. I can’t think of a scenario where a value and target value will both be strings and I am wondering if that is why an issue exists. You could maybe do something where you are using the 1s and 0s and then grouping by ‘Available’ and ‘Unavailable’ through something like conditional formatting. Or have that string field as a 3rd field to use as a groupBy. If you have any further questions about this please let me know, but hopefully we can find a good solution!

1 Like
7

Hello @Fotis_flex, I will mark the above response as the solution for now! If you have follow-up questions, feel free to send them over, or post a new question in the community with some things you have tried. That will also ensure you are at the top of the priority list for a response from one of our QuickSight experts, since we prioritize questions with 0 replies. Thank you and good luck!

8

Hallo @DylanM . Happy new year!
I am using a gauge chart. In field well, I choose the calculated field from formula above with the ambition to display either green color or red based on a binary value. Together we the color, I would also like to illustrate a text value Available/Unavailable. Anyway Dylan, let me look it a bit more and I will come back. Thanks a lot for your quick replies.

1 Like