topic Calculated Dimension with a date field in QlikView

Calculated Dimension with a date field

drohm002 — Thu, 07 Jan 2021 16:28:18 GMT

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!

Re: Calculated Dimension with a date field

edwin — Thu, 07 Jan 2021 17:06:02 GMT

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

Re: Calculated Dimension with a date field

drohm002 — Thu, 07 Jan 2021 19:31:56 GMT

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

Re: Calculated Dimension with a date field

edwin — Thu, 07 Jan 2021 21:13:57 GMT

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