Calculating Items Ordered During Shifts

[#1]

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

[vvvvvvizard]

The class function might be useful here

[marcelviegas]

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

[devarasu07]

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

[Colin-Albert]

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

;