Calculated Dimension with a date field

[drohm002]

Hi, I have a field called OUT_ROOM_DT_TM.  See below. I want to create a calculated dimension that displays only the last instance for each day.  So it would display 1/3/2021 9:55pm, 1/2/2021 9:44pm, and 1/1/2021 8:32pm.  Please help!

[edwin]

you can add this info in your script, add a DATE field, DATE(floor(OUT_ROOM_DT_TIME)) as OUT_ROOM_DT, then left join your table with:

noconcatenate
load OUT_ROOM_DT, timestamp(max(OUT_ROOM_DT_TIME)) as maxTime resident YOURTABLE group by OUT_ROOM_DT

this will get the latest time per day and add it to your table (you can drop the OUT_ROOM_DT field if you wont need it

[drohm002]

Hi there, thank you for your response.  I already have an OUT_ROOM_DT field, just a little fyi.  Is there anyway to achieve what i am trying to do by just creating a calculated dimension?  I am not very good with joining tables and such inside of the script.  thank you for your time

[edwin · Accepted solution]

adding the date field and the actual max timestamp in the data table IMO is an efficient way to determine the info you are looking for. 

if you dont want to mess with the script, then you can create a chart with OUT_ROOM_DT as your dimension and timestamp(max(OUT_ROOM_DT_TM)) as your expression.

this would do the aggregation in the chart instead of the data model