Count by hours??

[#1]

Hi All,

I have a requirement where i need to put a chart like Count and Sum by only hour irrespective of date, example below

01/02/2014 00:12:25

01/02/2014 00:30:29

02/02/2014 01:40:25

05/02/2014 01:18:25

03/02/2014 02:13:25

09/02/2014 02:16:25

25/02/2014 02:54:25

so when i take count, the output should be below

hour   count

00       2

01       2

02       3

so please help me achieving this!!

[Anonymous]

The easiest way would be to create a new field in the load script called "hour".

You can easily use the Hour() function to achieve this.

eg, if DateTime is your existing field, something like this would then allow a count to be preformed on the new "hour" field.

LOAD

    DateTime,

    Hour(DateTime) as hour

FROM...

You can then easily create a bar chart for example of hour by count(hour).

I've attached a very simple example with your data.

[#3]

Hi,

Have a look at the attached file.

Hope it helps!

Kiru

[Anonymous]

Hi,

Hope  it helps !

Load

mid(field_date,12,2) as Hour,

hour(field_date) as Hour_Count

From....

[maxgro]

use a calculated dimension, if your field is datetime

=time(floor(frac(datetime),1/24), 'hh')

expression

=count(datetime)

[Anonymous]

Hi Shrihari,

Your requirement for count and sum of hour,it's unclear.

if you want to count of how many hours  an work is done.

you need to take a max hour of each day and sum it.

then you might achieve the requirement you need.

Nitha

[Anonymous]

Hi Shrihari J, could you please confirm if this is a Qlik Sense or QlikView problem they are having?

This has been posted in the Qlik Sense forum, however a couple of these suggestions are relating to QlikView.

[#8]

Thank you all for the replies. used 'Hour(datetime) as Hour' while loading, its working fine.