Error in ifelse Function: Incorrect Argument Type When Checking Week Off Days in QuickSight

Question & Answer
, , , ,
1

I’m trying to create a QuickSight analysis to determine if associates are expected to be online during their working hours. The data includes a column {week Off} that lists the days off for each associate in a comma-separated format (e.g., Mon,Tue). I’m using the following ifelse expression to check if today is a week off:


ifelse(
  contains(trim({week Off}), formatDate(now(), 'EEE')), 
  'Off', 
  'On'
)

Data Sample
Data Sample1617×493 243 KB

However, I’m encountering the following error:

Error Message:
“At least one of the arguments in this function does not have the correct type.”

I’ve ensured that the {week Off} column is a string and that formatDate(now(), ‘EEE’) returns the correct day abbreviation (e.g., Mon). Despite this, the contains function doesn’t seem to work correctly when used with these arguments. I’m looking for help to identify what might be causing this issue and how to resolve it.

2

Hi @isingla,

welcome to the Community!

I think the Error is, that the argumenttype ‘EEE’ is not supported in the formatDate formula.

BR
LKS

1 Like
3

Hi @LKS,

Thanks for your response. The issue wasn’t with the EEE format. I was able to identify and resolve the problem.

The contains function only supports checking one column at a time and can’t compare two columns simultaneously. Instead, I used the locate function, which allowed me to get the desired results.

ifelse(
locate(toString({week Off}), formatDate(now(), ‘EEE’), 1) > 0,
‘Off’,
‘On’
)

2 Likes