Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
alec1982
Specialist II
Specialist II

Extract Time and Round it to the hour from timestamp

have a column with timestamp and want to extract the time and round it to the hour. I am getting the right results but see duplicates of the hours in a list box or filter. anyone has a solution.

Formula I am using: time(round(timestamp(FieldName,'MM/DD/YYYY h') 1/24),'hh:mm')

 

4 Replies
Arthur_Fong
Partner
Partner

You should build this at script level: time(round(timestamp(FieldName,'MM/DD/YYYY h') 1/24),'hh:mm') as Time

 

Else maybe try this:

=AGGR(only(distinct time(round(timestamp(FieldName,'MM/DD/YYYY h') 1/24),'hh:mm')),time(round(timestamp(FieldName,'MM/DD/YYYY h') 1/24),'hh:mm'))

alec1982
Specialist II
Specialist II
Author

I am building the logic in the script and the formula you have is exactly what I have. it works but it shows duplicates on list box in qlikview or filter in qlik sense. need to clean up the duplicates.

Arthur_Fong
Partner
Partner

You have a sample app?

Kushal_Chawda

can you try below

Time#(hour(round(time(Time,'hh:mm'), 1/24))&':'&00,'hh:mm') as Time