topic Re: Calculating Items Ordered During Shifts in QlikView

Calculating Items Ordered During Shifts

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am looking to calculate how many items were bought within a specific time slot and bucketing them by shift. I am unable to get Qlik to calculate hours between 23:00 and 07:00 for the 3rd shift. Can someone explain how I can achieve this? Expression below.

Dimension:

=If([Items] = 'Shirt' and Timestamp#([Ordered Date & Time], 'hh:mm:ss') >'07:00' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')<='15:00', '1st Shift',

If([Items] = 'Shirt' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')>'15:00' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')<='23:00', '2nd Shift',

If([Shirt] = 'Shirt' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')>'23:00' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')<='07:00', '3rd Shift', 'Total')))

Measure:

Count(Items)

Thanks,

Kurleigh

Re: Calculating Items Ordered During Shifts

vvvvvvizard — Thu, 24 Aug 2017 12:44:21 GMT

The class function might be useful here

Re: Calculating Items Ordered During Shifts

marcelviegas — Thu, 24 Aug 2017 12:45:17 GMT

hi,

=If([Items] = 'Shirt' and Timestamp#([Ordered Date & Time], 'hh:mm:ss') >'07:00' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')<='15:00', '1st Shift',

If([Items] = 'Shirt' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')>'15:00' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')<='23:00', '2nd Shift',

If([Shirt] = 'Shirt' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')>'23:00' and Timestamp#([Ordered Date & Time], 'hh:mm:ss')<='07:00', '3rd Shift', 'Total')))

Modifiable and for or

Re: Calculating Items Ordered During Shifts

devarasu07 — Thu, 24 Aug 2017 13:06:43 GMT

Like this?

if([Items] = 'Shirt' and Hour(DateTime)>7 and Hour(DateTime)<=15,'1st Shift',

if([Items] = 'Shirt' and Hour(DateTime)>15 and Hour(DateTime)<=23,'2nd Shift ',

if([Items] = 'Shirt' and Hour(DateTime)>23 and Hour(DateTime)<=07,'3rd Shift ','Total')))

Re: Calculating Items Ordered During Shifts

Colin-Albert — Thu, 24 Aug 2017 13:16:33 GMT

I think you need to consider the date the shift starts as well as time.

It would be much simpler to create a Shift Number field in your data load, and a Shift Date from this shift number.

Your charts can then simply sum the data by Shift Date and Shift Number.

For example, the script below uses preceeding loads to set a Shift_Date to the date the shift starts, so an item ordered on 2017-08-24 at 0300 would be reported as shift 3 but with a shift date of 2017-08-23, the day before as the shift starts at 23:00 the previous day.

load

If(Shift_No = 3 and Order_TS < '07:00', date(floor([Ordered Date & Time])), date(floor([Ordered Date & Time]) -1) as Shift_Date

* ;

load

If(Order_TS >='07:00' and Order_TS <'15:00, 1,

If(Order_TS >='15:00' and Order_TS <'23:00, 2,

3)) as Shift_No,

* ;

load

Timestamp#([Ordered Date & Time], 'hh:mm') as Order_TS,

Items,

etc

from blah blah blah

;