In excel use if funtion how to use in quicksight?

Question & Answer
,
1

Hi everyone,

In excel can use an easy function =IF(AO2=“”,“”,AO2-AF2) to do the calculations,

AO is estimated_cargo_delivery_date, AF is order_day

but in quicksight only have ifelse function
my function : ifelse({estimated_cargo_delivery_date}=“”,“”,{estimated_cargo_delivery_date}-{order_day})
but it showed error, i don’t know how to do.

image
image739×101 1.59 KB

Thanks.
Chloe

2

Hello,
I think the difference is, In QuickSight function ifelse, the else part is mandatory,
So can you please try this :
ifelse({estimated_cargo_delivery_date}=“”,“”,{estimated_cargo_delivery_date}-{order_day}, NULL) ?

Regards

1 Like
3

error showed as :
Expression ifelse({estimated_cargo_delivery_date}=“”,“”,{estimated_cargo_delivery_date}-{order_day}) for function - has incorrect argument type Date - Date. Function syntax expects ‘ - ’
‘<DATETIME_INTERVAL> - <DATETIME_INTERVAL>’
‘ - <DATETIME_INTERVAL>’.

my two data format is MM/dd/yyyy HH:mm:ss, what can i do?

4

Thanks for your message! But still error, i guess data format issue.

5

the error should be caused by different data type returned between the if/else conditions. In your example, if estimated_cargo_delivery_date is null, it returns a string(null), else it returns a number(estimated_cargo_delivery_date - order_day).

you need to convert either one of them, so that they can be the same data type.

For instance,
ifelse(
isNull({estimated_cargo_delivery_date}), ‘’,
toString(dateDiff({estimated_cargo_delivery_date},{order_day},“DD”))
)

1 Like