Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
drohm002
Contributor
Contributor

Calculated Dimension with a date field

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!

drohm002_0-1610036704009.png

 

1 Solution

Accepted Solutions
edwin
Specialist III
Specialist III

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

View solution in original post

3 Replies
edwin
Specialist III
Specialist III

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
Contributor
Contributor
Author

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
Specialist III
Specialist III

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

View solution in original post