topic Re: Time as a Dimension in QlikView

Time as a Dimension

tmumaw — Fri, 06 Jun 2014 17:33:04 GMT

Hi all,

I am trying to use the following as a dimension with no luck. What am I missing?

Thanks

time(date(Opportunity.CreatedOn,'YYYY/MM/DD hh:mm:Ss'),'hh TT') as Hour_Created

Example:

12 PM and then counting the number of opportunities created at 12 PM.

Re: Time as a Dimension

its_anandrjs — Fri, 06 Jun 2014 17:42:41 GMT

The expression seems ok if you try with

Eg:-

=Time(Date(now(),'YYYY/MM/DD hh:mm:ss'),'hh TT') //Same as your expression

Re: Time as a Dimension

tmumaw — Fri, 06 Jun 2014 17:46:02 GMT

The expression works perfect, but when I use Hour_Created as a dimension I get the following:

12 PM 1

Where is should be 12 PM 3

Re: Time as a Dimension

Clever_Anjos — Fri, 06 Jun 2014 17:53:20 GMT

You must get only time fraction

time(frac(Opportunity.CreatedOn),'hh TT') as Hour_Created

time(round(frac(Opportunity.CreatedOn),1/24),'hh TT') as Hour_Created if your field holds minutes too

Re: Time as a Dimension

its_anandrjs — Fri, 06 Jun 2014 17:56:58 GMT

Plot list box of Opportunity.CreatedOn and in expression write code

Len( time(date(Opportunity.CreatedOn,'YYYY/MM/DD hh:mm:Ss'),'hh TT') )

Check it is 5 or 7 if it is 5 then ok but 7 need to check your load script.

Re: Time as a Dimension

Fri, 06 Jun 2014 18:10:39 GMT

try this

date(date#(Opportunity.CreatedOn,'YYYY/MM/DD hh:mm:ss'),'hh TT')

Re: Time as a Dimension

tmumaw — Fri, 06 Jun 2014 18:14:36 GMT

It's 5

Re: Time as a Dimension

tmumaw — Fri, 06 Jun 2014 18:15:45 GMT

Nothing in Hour_Created.....

Re: Time as a Dimension

its_anandrjs — Fri, 06 Jun 2014 18:21:38 GMT

Try one of this

=Time(Timestamp(Opportunity.CreatedOn,'YYYY/MM/DD hh:mm:ss'),'hh TT') as Hour_Created

=Time(Date(Opportunity.CreatedOn,'YYYY/MM/DD hh:mm:ss'),'hh TT')

=Date(Date#(Opportunity.CreatedOn,'YYYY/MM/DD hh:mm:ss'),'hh TT')

Re: Time as a Dimension

Fri, 06 Jun 2014 18:32:03 GMT

OK, PLEASE TRY ACCORDING TO THIS

=date(date#(

now()

,'MM/DD/YYYY hh:mm:ss TT'),'hh TT')

Re: Time as a Dimension

MarcoWedel — Sat, 07 Jun 2014 01:28:53 GMT

Hi Thom,

the trick is to let only the hour part of your timestamp count as a distinct value.

All formatting time functions (time, date, timestamp) do not change the numerical representation of your Opportunity.CreatedOn timestamp and therefore are not sufficient per se.

Therefore you have to round or truncate the timestamp to full hours or extract it's hour part.

Possible solutions are e.g.:

a not functioning example would be:

because this expression does not remove the differences of the minutes part of the timestamp.

hope this helps

regards

Marco

Re: Time as a Dimension

tmumaw — Mon, 09 Jun 2014 15:08:16 GMT

Thanks Marco,

That was exactly what I was looking for.

Thom

Re: Time as a Dimension

MarcoWedel — Mon, 27 Oct 2014 22:39:44 GMT

You're welcome

regards

Marco

Re: Time as a Dimension

Mon, 27 Oct 2014 23:46:34 GMT

how do i create a hourly list box ?

Re: Time as a Dimension

MarcoWedel — Mon, 27 Oct 2014 23:56:06 GMT

open a thread and describe your requirements in detail

Re: Time as a Dimension

Tue, 28 Oct 2014 00:16:09 GMT

This is my thread how to create master calender link with bi-hourly time

you have provided a solution, but since i have to join 3 tables and each table has timestamp filed. I am able to create master calender but the issue time. Here is my list box from date and time. I would like to know why iam not getting unique time .