Greetings,
I am trying to create a calculated field where i am using a column ,called “requestdatetime” and it is of data type datetime, to find the latest date .
Thus i used the function : max({requestdatetime}), but i am getting an entire column displaying “unavailable”.
The format of this datetime column is : dd/MMM/yyyy:HH:mm:ss Z , example (2023-05-25T00:04:35.000Z).
I suspect that max() function is unable to convert this column due to format.
I have checked for nulls, there are none.
Hi @Prathik - Welcome to AWS QuickSight Community and thanks for posting the question. At data set level, max function will show the column value as unavailable but when you will use it in Analysis, the value will show up. Please test it in Analysis using the data set. Hope this will fix your issue.
Regards - Sanjeeb
Hi @Sanjeeb2022 thank you for your response. I have a doubt regarding my usecase. I have a dataset which has “employee_id” and “time_entered_office”, there are multiple records for each employee. I wanted to create a visual where a person can filter out the employee id and in return just get single value, that is the most recent date that an employee came into the office.
So from you above response about Max() function i went ahead and tried to create a calculated field called “latest_date” with the function maxOver(max(time_entered_office),[employee_id]).
So my question is how do i best visualize this usecase.
– Sorry, if this is a silly doubt, i am new to visualization itself
Hi @Prathik - You are correct, if you are looking for the maximum time per employee, we have to use maxOver function. Create a calculated field for the same and use in table chart and put employee id as dimension and this calculated field as measure and then you can validate the data. You can also put the entry time in dimension to ensure for each employee id, entry time may vary but this calculated field will give you maximum of entry time for that employee.
Hope this provide some details to you.
Regards - Sanjeeb
Thank you for your help!!