Time Buckets in Bar Chart

[#1]

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

[sunny_talwar]

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

[swuehl]

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])

[sunny_talwar]

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

[MarcoWedel]

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

[Saravanan_Desingh]

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);

[#7]

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 !

[sunny_talwar · Accepted solution]

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);

[#9]

Thanks

[qliksus]

You can also achieve this using Class

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