@Dawid , sorry for not being very clear on the requirement. (i read my initial question it is condescending and the solution you have is perfect.)
What we were trying to do was on a Looker UI and specifically using Table Calculations, we didnt want to create a Derived table and additional explore for a reason.
After several digging i was able to find a solution that resolved my issue.
Core Problem : Finding a Max Date in a given partitioned window, the problem is the date time format and finding an max value in it.
Preparation :
Convert the date to a unique number, (since in our case we are using mySQL we created a dimension as follows :
dimension: end_time_value {
type: number
sql: unix_timestamp(${TABLE}.end_time) ;;
}
Add the following Dimensions to the Look
Client
End Date
End Date Value(Unix Timestamp)
Create a Table Calculation “partition_row_by_client”for creating a Row Partition
if (
match(${table.client},${table.client})=offset(match(${table.client},${table.client}),-1),
1+row()-match(${table.client},${table.client}),
1)
Create a Table Calculation “max_unix_timestamp_by_client” (This is to get the Max value in the given partition)
Create a Table Calculation to Lookup the Date Time (The reason why we do this step is, Step4 only brings the Unix Timestamp and probably you wouldnt want your visualization. )