Extract Time and Round it to the hour from timestamp

[alec1982]

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')

[Arthur_Fong]

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]

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]

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