topic Re: Time Buckets in Bar Chart in QlikView

Time Buckets in Bar Chart

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

How to do this task

am having order time now i need to creat trende a bar chart to show order booking .(what is the peak time of booking)

on x axis time buckets like 6-10,10-14,14-18,18-22 on Y axis Booking Trend

PFA Excel. !

Re: Time Buckets in Bar Chart

sunny_talwar — Mon, 23 May 2016 17:25:51 GMT

May be use an if statement:

If([Order Date] >= MakeTime(6) and [Order Date] < MakeTime(10), '6-10',

If([Order Date] >= MakeTime(10) and [Order Date] < MakeTime(14), '10-14',

If([Order Date] >= MakeTime(14) and [Order Date] < MakeTime(18), '14-18',

If([Order Date] >= MakeTime(18) and [Order Date] < MakeTime(22), '18-22'))))

Assuming [Order Date] is correctly read as a time field

Re: Time Buckets in Bar Chart

swuehl — Mon, 23 May 2016 17:39:21 GMT

Another option could be

MAP:

MAPPING

LOAD Start+Iterno()-1 as Hour, Dual(Period, Start) As Period INLINE [

Period,Start, End

before 6, 0,5

6-10,6,9

10-14,10,13

14-18,14,17

18-22,18,21

After 22, 22,23

]

While Start+Iterno()-1 <= End;

LOAD [Order Number],

[Order Date],

ApplyMap('MAP', Hour([Order Date])) as GroupedTime

FROM

[Order Details.xlsx]

(ooxml, embedded labels, table is Sheet1);

Then use GroupedTime as dimension and as expression e.g.

=Count(DISTINCT [Order Number])

Re: Time Buckets in Bar Chart

sunny_talwar — Mon, 23 May 2016 17:49:00 GMT

One more enhancement which comes to mind is to do something like this:

If([Order Date] >= MakeTime(6) and [Order Date] < MakeTime(10), Dual('6-10', 1),

If([Order Date] >= MakeTime(10) and [Order Date] < MakeTime(14), Dual('10-14', 2),

If([Order Date] >= MakeTime(14) and [Order Date] < MakeTime(18), Dual('14-18', 3),

If([Order Date] >= MakeTime(18) and [Order Date] < MakeTime(22), Dual('18-22', 4)))))

Re: Time Buckets in Bar Chart

MarcoWedel — Mon, 23 May 2016 20:20:04 GMT

Hi,

another solution to create the time buckets as either calculated dimension in the chart or field in the script could be:

calculated dimension:

=Dual(Time(Floor([Order Date],'04:00','06:00'),'hh')&'-'&Time(Floor([Order Date],'04:00','06:00')+'04:00','hh'),Floor([Order Date],'04:00','06:00'))

script:

table1:
LOAD [Order Number],
    [Order Date],
    Dual(Time(Floor([Order Date],'04:00','06:00'),'hh')&'-'&Time(Floor([Order Date],'04:00','06:00')+'04:00','hh'),Floor([Order Date],'04:00','06:00')) as TimeBucketField
FROM [https://community.qlik.com/servlet/JiveServlet/download/1045624-227452/Order%20Details.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

Re: Time Buckets in Bar Chart

Saravanan_Desingh — Tue, 24 May 2016 02:15:15 GMT

One more version of swuehl‌, using LookUp..

MAP:
LOAD Start+Iterno()-1  as Hour, Dual(Period, Start) As Period INLINE [
Period,Start, End
before 6, 0,5
6-10,6,9
10-14,10,13
14-18,14,17
18-22,18,21
After 22, 22,23
]
While Start+Iterno()-1 <= End;
LOAD [Order Number], 
     [Order Date],
  LookUp('Period', 'Hour', Hour([Order Date]), 'MAP') as GroupedTime
FROM

(ooxml, embedded labels, table is Sheet1);

Re: Time Buckets in Bar Chart

Tue, 24 May 2016 19:09:47 GMT

Hi sunny,

I got one issue in bucket creation.not able to show 24 hours time buckets eg-makedate(0) is 24 but bucket not working

PFA !

Re: Time Buckets in Bar Chart

sunny_talwar — Tue, 24 May 2016 19:13:20 GMT

Try this:

LOAD [Order Number],

[Order Date],

If([Order Date] >= MakeTime(1) and [Order Date] < MakeTime(5), '1-5',

If([Order Date] >= MakeTime(5) and [Order Date] < MakeTime(15), '5-15',

If([Order Date] >= MakeTime(15) and [Order Date] < MakeTime(20), '15-20',

If([Order Date] >= MakeTime(20) and [Order Date] <= MakeTime(23, 59, 59), '20-24')))) as Bucket

FROM

[Order Details.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Time Buckets in Bar Chart

Tue, 24 May 2016 19:18:48 GMT

Thanks

Re: Time Buckets in Bar Chart

qliksus — Thu, 26 May 2016 03:15:00 GMT

You can also achieve this using Class

Calculated Dimension: class(left([Order Date],FindOneOf([Order Date],':')-1),4,'',6)