Cannot return calculated field into string

Question & Answer
, ,
1

I have the expression below. Take Off (Act) and Take Off (Est) are date data types and selectedMonth and selectedYear parameters are strings. However the expression does not return a string and I when I try to insert it into a table visual I get error, unsupported date. Why?

ifelse(
    isNotNull(coalesce({Take Off (Act)}, {Take Off (Est)}))
    AND 
    parseDate(formatDate(addDateTime(-12, 'MM', parseDate(concat('01-', ${selectedMonth}, '-', ${selectedYear}), 'dd-MMM-yyyy')), 'yyyy-MM-dd'), 'yyyy-MM-dd') <= coalesce({Take Off (Act)}, {Take Off (Est)})
    AND 
    coalesce({Take Off (Act)}, {Take Off (Est)}) <= parseDate(concat('01-', ${selectedMonth}, '-', ${selectedYear}), 'dd-MMM-yyyy'),
    toString(formatDate(coalesce({Take Off (Act)}, {Take Off (Est)}), 'MMM-yyyy')),
    toString('NO')

)
2

Hi @Fotis_flex

Your expression looks fine; but there may be some naunces to how things work.

I would create the following three calculated fields to test and bring them one at a time into the Pivot to check which ones breaks and then evalulated the one giving problems further.

  1. coalesce({Take Off (Act)}, {Take Off (Est)})
  2. parseDate(concat(‘01-’, ${selectedMonth}, ‘-’, ${selectedYear}), ‘dd-MMM-yyyy’)
  3. parseDate(formatDate(addDateTime(-12, ‘MM’, parseDate(concat(‘01-’, ${selectedMonth}, ‘-’, ${selectedYear}), ‘dd-MMM-yyyy’)), ‘yyyy-MM-dd’), ‘yyyy-MM-dd’)
1 Like
3

Hi @Giridhar.Prabhu Thanks for your reply. I already did this. I solved the problem. For some reason the error was in line
toString(formatDate(coalesce({Take Off (Act)}, {Take Off (Est)}), 'MMM-yyyy'))

I substituted it with substring((formatDate(coalesce({Take Off (Act)}, {Take Off (Est)}), 'dd-MMM-yyyy')),4,8), and worked!

[Update from QuickSight team : MMM-yyyy isn’t a supported format whereas dd-MMM-yyyy is a supported one. Full list of supported date formats is available here - Supported date formats - Amazon QuickSight ]

1 Like
4

Great to hear the problem is solved!

5

Hi @Fotis_flex ,
Glad to know that you were able to resolve your issue.
I have updated your last response with some additional info and am marking it as solution now.

Regards,
Arun Santhosh
Pr QuickSight SA

1 Like