Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to extract hour from date column

I have a date column (Calendar_Dt) and want to exactract Hour from that column.i need to use this hour column in dimensions to display as 00 to 23, the complete 24 hours has to display in bar chart. So that based on hour i am calucating answered, abandoned calls like that.. can anybody help me on this. thank you.

1 Solution

Accepted Solutions
Digvijay_Singh

May be your date field is not having time part which you used in the script. I think if you use time or timestamp type, it will show correct hour.

If you try below function in textbox it shows 05 as hour.

=Hour(Timestamp#('20-07-2016 05:57:10'))

View solution in original post

10 Replies
sunny_talwar

Have you tried using Hour() function:

Hour(Calendar_Dt) as Hour

or

Calculated dimension:

Hour(Calendar_Dt)

Not applicable
Author

thanks for your reply sunny

Yes i have used it under fiscal_calendar dimension at script level and reloaded the application, its showing only '0' for that particular column 'Hour'

When i use Hour column i want to get the values like 00,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23 to show the number of calls for each hour.

Any other solution which we can get desired output?

Not applicable
Author

And currently i dont have date column in timestamp format thats what am using below expression to convert it to hours

=Time(num((Calendar_Dt)/24),'hh'), when i use this expression in calculated dimension, am getting the number of hours from 00 to 23 but getting hour values for all dates which Calendar_Dt has. How to get the hour values only one time to display constantly, can we distinct these values anywhere or can we use any function to get values only once in chart.

Please advise. thank you

Srini

Not applicable
Author

Hi,

Try this

=Time(Hour(Date))

// I think Your Date field must contains time as well to find Hours

Digvijay_Singh

May be your date field is not having time part which you used in the script. I think if you use time or timestamp type, it will show correct hour.

If you try below function in textbox it shows 05 as hour.

=Hour(Timestamp#('20-07-2016 05:57:10'))

avinashelite

check whether your OS time format is same as the Calendar_Dt format and also check your Calendar_Dt date format .. if still issue persist share some sample data

saimahasan
Partner - Creator III
Partner - Creator III

Hi

You can try using the expression:

=Hour(Timestamp(DateField))

where DateField should be proper DateTime format.

Not applicable
Author

Unfortunately i dont have the date column with timestamp format to extract hour. i checked at DB level, there we have the time format for different  columns but not for Calendar_Dt column, so could i use any other column which will have time stamp?

Digvijay_Singh

You can use any column as long as it has time and it serves the business purpose