Create Date Calculated Field

Question & Answer
, ,
1

Hello All,
I am getting familiarized with Quicksight and I am having trouble creating a calculated field as a Date format, which I intend to use in a comparison with other dates.

Exercise below:
image

  1. “Last departure” is a calculated field that pulls the values (date format) from a table using:
    maxOver(max({ftt_actualdeparturetime}),[{ftt_tail_no}])
  2. “Today” column is the function NOW() an therefore also a date.
  3. “Days since last ops” is the difference of the two:
    dateDiff({Last departure},now(),‘DD’)
  4. For specific tail EI-STV, the actual “Last Departure” should be “2023-08-27” but this particular event is not captured in the table.

How can I best overwrite the value manually and ensure that “Days since last ops” is calculated with respect to my manual input only when my manual input is greater than whatever date is pulled from the table?

I had thought to create a calculated field called “Manual” to assign the desired value “2023-08-27” to this tail, and then to amend the “Last departure” calculated field to select the latest value between “Manual” input and the table, but I cant figure it out.

Probably basic for the mighty Quicksight experts around. Your help is much appreciated. Thanks!

2

Hello @pablosf - Welcome to the community! Thank you for posting your query. If I understood your query, it seems you are trying to enforce a date manually by providing your own input. You can do that by creating a calculated field using the following expression. Hope this helps!

image

3

Hi @sagmukhe thanks a lot for the quick reply.

That indeed works across the board, but I receive error when I try to embed your logic into an IfElse.

Note how I want “2023-08-27” for “EI-STV” and I went with a dummy “2023-01-01” for all other tails.

image

The error is unfortunately not very clear - it just says to correct and try again.

4

@pablosf - It seems this is encountering error due to mismatch in datatypes between the if and else result section. In “if” portion, parseDate() function generates output in “date” datatype whereas you have hardcoded the “else” portion as string (in double string). Try to put the “2023-01-01” also in parseDate() function similar to the example that I provided. Hopefully that should alleviate your problem.

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

5

Thanks again @sagmukhe. That solved part of the problem and I was able to save changes to “Manual” based on your suggestion.

However, it seems that the calculated field “Manual” is not properly recognized as a date.
image

This is preventing the use of this field to calculate “Last departure” as per below:

dateDiff(ifelse(dateDiff({Last departure},{Manual Ferries})>0,{Last departure},{Manual Ferries}),now(),‘DD’)

Would you have any hints?