Shift reporting: using time intelligence for night shift?

[arnebrackeParticipant Started: 3Replies: 4Total: 7]

Hi P3 folks,

I have built a dashboard to report the “number of items produced per shift”.

The production manager selects a year / month / day and a machine. The report displays 3 tables + 3 charts, one per shift:

• early shift: 5am – 1pm
• late shift: 1pm – 9pm
• night shift: 9pm – 5am

This works fine for the early / late shift. For the night shift (as this one crosses mighnight), it displays data from two different shifts (normal time intelligence functionality…):

• 0am – 5pm: results of Shift N
• 9pm – 12pm: result of Shift N+1

Any advice how to use time intelligence, but having an exception for the night shift? I except the measures for the nightshift to display data for Shift N, i.e. (Day N-1) 21:00 – (Day N) 5:00.

Printscreen of the dashboard in attachment.

Thanks for your feedback!

 

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

An interesting challenge, which could be easily handled depending on your version of Excel (2010, 2013 or 2016) and your data model.

What version of Excel are you using?

Could you post a screen-shot of your model? Better yet, if you could attach a workbook with some sample (fictitious) data over a continuous 3 or 5 day period. The data is not important, but your model should be consistent with production.

In addition to your basic shifts (Night, Early, and Late), for each day you really have 4 shift details to handle: Previous Night, Early, Late, This Night.

Tom

[arnebrackeParticipant Started: 3Replies: 4Total: 7]

Hi Tom,

Thanks for your reply.

I’m using Excel 2016. But I would be interested in hints on solutions for other versions as well: what if the end-user of the dashboard does not have Excel 2016?

I have added a ZIP-file with the workbook containing data for 3 days (October 4, 5 and 6), machine PU3.

You are correct when you say we have 4 shift types to deal with. This is confirmed by my definition of  the calculated column T_Facts[Shift]:

T_Facts[Shift]==SWITCH( TRUE()
; [PRODUCTIETIJD (Hour)] <= 5; “NACHT”
; [PRODUCTIETIJD (Hour)] <= 13; “VROEG”
; [PRODUCTIETIJD (Hour)] <= 21; “LAAT”
; “NACHT”
)

(Translation: “NACHT” = Night; “VROEG” = Early; “LAAT” = Late).

Regards,
Arne

 

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Arne,

Your table for shifts shows:

The early shift from 5 to 13, the late shift from 13 to 21 and night from 21 to 5.

I believe the formula for shift should be:

T_Facts[Shift] =
SWITCH (
    TRUE ();
    [PRODUCTIETIJD (Hour)] < 5; "NACHT";
    [PRODUCTIETIJD (Hour)] < 13; "VROEG";
    [PRODUCTIETIJD (Hour)] < 21; "LAAT";
    "NACHT"
)

Agree/disagree?

Tom

[arnebrackeParticipant Started: 3Replies: 4Total: 7]

Hi Tom,

I agree: that is exactly how I have defined the formula for shift (Calculated Column). See printscreen in attachment.
(Mark that I used “<=” instead of  “<“, but that is just a detail…)

Arne

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Arne,

Please see attached workbook. I created a column for shift start date, and changed the relationship from the calendar to the fact table based on shift start date.

I created a sort by column for the shift hour so that night shift hours would run from 21, 22, 23, 0, 1, 2, 3, 4.

Also modified columns and measures to work with the changes in the data model.

My totals are different than yours, but I believe the reason is that I calculated shifts with the < instead of =<. The work was more than expected, but the concept should be valid for Power Pivot in Excel 2016, 2013 and 2010. Tom

Attachments:

You must be logged in to view attached files.

[arnebrackeParticipant Started: 3Replies: 4Total: 7]

Hi Tom,

This seems to work like a rockit! Many thanks, I learned a lot from your intervention…

Greetings from Belgium.

[Author]

Posts