Home › Forums › Power Pivot › Shift reporting: using time intelligence for night shift?
This topic contains 6 replies, has 2 voices, and was last updated by arnebracke 6 years, 5 months ago.
-
AuthorPosts
-
November 20, 2017 at 1:15 pm #8628
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.November 20, 2017 at 5:23 pm #8630An 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
November 20, 2017 at 7:26 pm #8632Hi 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,
ArneAttachments:
You must be logged in to view attached files.November 21, 2017 at 6:39 pm #8637Arne,
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
November 21, 2017 at 9:16 pm #8640Hi 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.November 22, 2017 at 3:53 am #8643Arne,
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.November 23, 2017 at 2:57 pm #8649Hi Tom,
This seems to work like a rockit! Many thanks, I learned a lot from your intervention…
Greetings from Belgium.
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.