topic Re: Count by hours?? in App Development

Count by hours??

Wed, 01 Oct 2014 07:35:53 GMT

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!!

Re: Count by hours??

Anonymous — Wed, 01 Oct 2014 08:57:18 GMT

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.

Re: Count by hours??

Wed, 01 Oct 2014 18:07:53 GMT

Hi,

Have a look at the attached file.

Hope it helps!

Kiru

Re: Count by hours??

Anonymous — Wed, 01 Oct 2014 20:18:25 GMT

Hi,

Hope it helps !

Load

mid(field_date,12,2) as Hour,

hour(field_date) as Hour_Count

From....

Re: Count by hours??

maxgro — Wed, 01 Oct 2014 20:39:39 GMT

use a calculated dimension, if your field is datetime

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

expression

=count(datetime)

Re: Count by hours??

Anonymous — Thu, 02 Oct 2014 18:23:32 GMT

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

Re: Count by hours??

Anonymous — Fri, 03 Oct 2014 09:26:18 GMT

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.

Re: Count by hours??

Mon, 06 Oct 2014 08:41:48 GMT

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