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

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

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

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

Text version (instead of HTML-converted version) of the tables used in my explanation:

Example of the incorrect calculated average:

WeekNum    ProdID    Quantity
1                      1                5
3                      1                10
8                      1                1
10                     1                5
52                      1                5

Weekly average Quantity for Product 1: 26/52 = 0,5
Mathematic average of above table: 26/5

Design of data table:

ProdId    Quantm3    ScanOut
40            1,596            11/07/2017 10:37

[Author]

Posts