topic How to create interval of time values in a chart in QlikView

How to create interval of time values in a chart

amber2000 — Fri, 26 Sep 2014 10:32:13 GMT

Hello Qlikview wizards,

I have a question about creating a Time interval in a chart.

On my main axe there are multiple time values (hh:mm:ss), I want to narrow the values down to get a cleaner overview.

I want the Nbr of pallets shown per half hour instead of each time value I have in my data..

Does anyone know how to do this please? I've attached a simple example.

Kind regards,

Ambertje

Re: How to create interval of time values in a chart

varunjain — Fri, 26 Sep 2014 10:58:59 GMT

PLEASE FIND ATTACHED.. I have created bucket based on your requirement i.e half an hour segmentation. Please let me know if its helpful

Re: How to create interval of time values in a chart

demoustier — Fri, 26 Sep 2014 11:19:59 GMT

maybe just changing the dimension to '=time(ceil(End_Time,.02083333333333333))'

Re: How to create interval of time values in a chart

amber2000 — Fri, 26 Sep 2014 11:51:13 GMT

It is helpfull but I would like the Aisles presented next to each other instead of stacked.

Could you also tell me what the bucket does and how the function works?

Later on I might want to change the interval to hours or quarter hours.

Thanks for your help

Re: How to create interval of time values in a chart

demoustier — Fri, 26 Sep 2014 12:02:17 GMT

- on graph propriety / style / you can choose 'stacked' or s'eparate'

-to display every hour, replace 0.020833333343035 by 60/1440=0.0416666666

-to display every quarter hours (15 mn), replace 0.020833333343035 by 15/1440 = 0.0104166666666

indeed, 1440 = amount of minuts in one day (24*60)

to display every X mn, you have to round your dimension by an offset of X/1440

0.0208333333 = 30/1440 and so display every half an hour

Re: Re: How to create interval of time values in a chart

demoustier — Fri, 26 Sep 2014 12:07:54 GMT

for an easier choice of your display, you can use a variable in the expression

=time(subfield(class(num(End_Time),$(v1)/1440,'ENDTIME'),' ',1))&'-'&time(subfield(class(num(End_Time),$(v1)/1440,'END TIME'),' ',6))

and manually enter the variable

Re: Re: How to create interval of time values in a chart

demoustier — Fri, 26 Sep 2014 12:09:11 GMT

Wahoo...clever !

Re: How to create interval of time values in a chart

varunjain — Fri, 26 Sep 2014 12:12:37 GMT

Bucket groups your data with a uniform segmentation.... If you wan to change the interval, change 0.020833333343035 to what interval you need...

BTW 0.020833333343035 is a result of num(time#('00:30:00','hh:mm:ss')) .. if you wan to change the iterval just change the '00:30:00' part and attain the numeric value of your time to use it in the CLASS function

Re: How to create interval of time values in a chart

amber2000 — Fri, 26 Sep 2014 12:19:40 GMT

It's working great, you guys are throughly amazing.

Thanks a million.

Someday I will like working with qlikview charts thanks to you all.

Re: How to create interval of time values in a chart

PrashantSangle — Fri, 26 Sep 2014 12:24:08 GMT

Hi,

Read this article written by Henric about creating Bucket.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/07/14/buckets

It will help you regarding your requirement.

Regards

Re: How to create interval of time values in a chart

amber2000 — Fri, 26 Sep 2014 12:27:34 GMT

One small question for the Aisle dimension, I would like to exclude the "PBL_ ......." values in the chart.

Because Aisle_Desc is not an expression I can't find the wright function to use.

Re: Re: How to create interval of time values in a chart

varunjain — Fri, 26 Sep 2014 12:37:11 GMT

Hey...Glad to help

Check if this helps

Re: How to create interval of time values in a chart

amber2000 — Fri, 26 Sep 2014 12:38:41 GMT

Thanks you so much Max.

Rreally good article for advanced users.

I will need to learn what all those functions do and play around with it.

Ambertje

Re: How to create interval of time values in a chart

Fri, 26 Sep 2014 12:40:24 GMT

Hi,

I had the same question for a while and the answer I got was little different but cool.

In that solution you can choose the time interval.

You can try it with this in the script:

TimeInterval:

LOAD * INLINE [

IntervalSize, IntervalName

6, 4 parts

24, Hour

48, 30 min

];

And make a listbox for TimeInterval.

And then this in the dimension.

=Aggr(

Dual(

(Time(Floor(timeStamp, 1/IntervalSize), 'hh:mm') & ' – ' & Time(Ceil(timeStamp, 1/IntervalSize), 'hh:mm')),

Floor(Frac(timeStamp), 1/IntervalSize)),

timeStamp)

Regards

// Fjalar

Re: How to create interval of time values in a chart

demoustier — Fri, 26 Sep 2014 12:42:00 GMT

the way i was looking for months !!!!!!!!!

Wonderful !

Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Re: How to create interval of time values in a chart

amber2000 — Fri, 26 Sep 2014 15:00:11 GMT

I will try this out and give you feedback

Re: How to create interval of time values in a chart

amber2000 — Fri, 21 Nov 2014 16:43:49 GMT

Hi Fjalar,

Finally I have the time to try out your suggestion.

I'm a little bit confused, a listbox doesn't contain dimensions so I can't add the expression.

I suppose you mean to add the expression in the chart I'm working on.

Am I wright to assume that I have to replace my expression Time(ceil([End_Time], 0.0416666666)) by your expression

=Aggr(

Dual(

(Time(Floor(timeStamp, 1/IntervalSize), 'hh:mm') & ' – ' & Time(Ceil(timeStamp, 1/IntervalSize), 'hh:mm')),

Floor(Frac(timeStamp), 1/IntervalSize)),

timeStamp

Kind regards,

Ambertje

Re: Re: How to create interval of time values in a chart

Sun, 30 Nov 2014 13:26:19 GMT

Hi Ambertje!
You probably misunderstood me. I use a listbox to change the type of timeInterval in a chart.
First, in the load script I do this:

TidsInterval:
LOAD * INLINE [
IntervalSize, IntervalName
24, Hour
48, Half hour
96, 15 mins ];

Then I make listbox were I choose the IntervalName field.
Then in the chart I use the loooong AGGR function.
Please look at the attached file

I really like to be able to change the time interval so this solution is perfect for me. Try this out 🙂

Fjalar