topic Re: how to extract hour from date column in QlikView

how to extract hour from date column

Wed, 20 Jul 2016 12:27:53 GMT

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.

Re: how to extract hour from date column

sunny_talwar — Wed, 20 Jul 2016 13:43:52 GMT

Have you tried using Hour() function:

Hour(Calendar_Dt) as Hour

Calculated dimension:

Hour(Calendar_Dt)

Re: how to extract hour from date column

Thu, 21 Jul 2016 04:42:05 GMT

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?

Re: how to extract hour from date column

Thu, 21 Jul 2016 07:16:16 GMT

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

Re: how to extract hour from date column

Thu, 21 Jul 2016 07:25:22 GMT

Hi,

Try this

=Time(Hour(Date))

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

Re: how to extract hour from date column

Digvijay_Singh — Thu, 21 Jul 2016 07:30:11 GMT

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

Re: how to extract hour from date column

avinashelite — Thu, 21 Jul 2016 07:33:10 GMT

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

Re: how to extract hour from date column

saimahasan — Thu, 21 Jul 2016 11:02:44 GMT

You can try using the expression:

=Hour(Timestamp(DateField))

where DateField should be proper DateTime format.

Re: how to extract hour from date column

Thu, 21 Jul 2016 17:33:53 GMT

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?

Re: how to extract hour from date column

Digvijay_Singh — Thu, 21 Jul 2016 18:17:07 GMT

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

Re: how to extract hour from date column

aj0031724 — Thu, 06 Apr 2017 07:43:45 GMT

Dear Avinash ,

Can you advise me with the below request from posted thread yeterday .How can I build the logic?

STRAIGHT TABLE DATE CAN BE MAPPED WITH 0-23 hours and then do the logic based on HOUR filter.?