Calculate Average......

[prees959]

Hi,

I need to calculate the average productivity of a particular Day, Shift Type, Grade and Dept...

For example, I have a dataset :

Date	Day	Grade	Dept	Shift Type	Units Produced
01/01/2016	Monday	1	A	Day	35
02/01/2016	Tuesday	2	B	Afternoon	53
03/01/2016	Wednesday	4	C	Night	24
04/01/2016	Thursday	2	C	Afternoon	67
05/01/2016	Friday	3	A	Day	35
06/01/2016	Saturday	3	B	Afternoon	24
07/01/2016	Sunday	5	C	Night	64
08/01/2016	Monday	1	A	Day	64
09/01/2016	Tuesday	4	C	Day	23
10/01/2016	Wednesday	6	A	Day	26
11/01/2016	Thursday	2	A	Afternoon	71
12/01/2016	Friday	5	A	Night	15
13/01/2016	Saturday	7	B	Afternoon	84
14/01/2016	Sunday	2	C	Afternoon	43
15/01/2016	Monday	4	C	Night	56
16/01/2016	Tuesday	6	A	Day	32
17/01/2016	Wednesday	8	C	Night	79
18/01/2016	Thursday	3	A	Night	83
19/01/2016	Friday	4	A	Afternoon	26
20/01/2016	Saturday	6	C	Afternoon	83
21/01/2016	Sunday	8	A	Day	23
22/01/2016	Monday	2	C	Night	57
23/01/2016	Tuesday	3	A	Night	24
24/01/2016	Wednesday	1	A	Day	74

So I would need to show that, for example,  Grade 1 Staff in Department A on a Day Shift have an average production of 49.5 etc.  

Is there a way to calculate this as an expression?

Thanks,

Phil

[sunny_talwar]

How are you getting 49.5 for Grade 1, Dept A, & Shift Type Day? I see three rows with value 35, 64, and 74. And they total to be 175 and 175/3 = 57.67? What am I missing?

[skamath1]

Add the following expression in the measure

Avg( {$<[Shift Type] = {"Day"}  >} [Units Produced])

[prees959]

Hi I also filtered on the day of the week 'Monday' ...

[sunny_talwar]

So, if you add Grade, Dept, Shift Type, & Day as dimension and Avg([Units Produced]) as your expression... it seems to give what you want

[prees959]

Hi,  thanks for the reply.

What I need to show is if an introduction of a new Rota will improve Production based on the same Day, Grade, Dept, Shift  Type.  The new shift Rota will be marked from a point in time (in the below example 26/01/206):

Date	Day	Grade	Dept	Shift Type	Units Produced	Shift Rota
01/01/2016	Monday	1	A	Day	35	A
02/01/2016	Tuesday	2	B	Afternoon	53	A
03/01/2016	Wednesday	4	C	Night	24	A
04/01/2016	Thursday	2	C	Afternoon	67	A
05/01/2016	Friday	3	A	Day	35	A
06/01/2016	Saturday	3	B	Afternoon	24	A
07/01/2016	Sunday	5	C	Night	64	A
08/01/2016	Monday	1	A	Day	64	A
09/01/2016	Tuesday	4	C	Day	23	A
10/01/2016	Wednesday	6	A	Day	26	A
11/01/2016	Thursday	2	A	Afternoon	71	A
12/01/2016	Friday	5	A	Night	15	A
13/01/2016	Saturday	7	B	Afternoon	84	A
14/01/2016	Sunday	2	C	Afternoon	43	A
15/01/2016	Monday	4	C	Night	56	A
16/01/2016	Tuesday	6	A	Day	32	A
17/01/2016	Wednesday	8	C	Night	79	A
18/01/2016	Thursday	3	A	Night	83	A
19/01/2016	Friday	4	A	Afternoon	26	A
20/01/2016	Saturday	6	C	Afternoon	83	A
21/01/2016	Sunday	8	A	Day	23	A
22/01/2016	Monday	2	C	Night	57	A
23/01/2016	Tuesday	3	A	Night	24	A
24/01/2016	Wednesday	1	A	Day	74	A
25/01/2016	Thursday	2	C	Afternoon	40	B
26/01/2016	Friday	4	A	Afternoon	5	A
27/01/2016	Saturday	6	C	Afternoon	50	B
28/01/2016	Sunday	2	C	Afternoon	20	B

So, is it possible to show that :

Date	Day	Grade	Dept	Shift Type	Units Produced	Shift Rota	Production Difference
04/01/2016	Thursday	2	C	Afternoon	67	A
25/01/2016	Thursday	2	C	Afternoon	40	B	-40%

for the same Day, Grade, Dept & Shift Type Rota B is 40% less efficient??

I will also need to be able to roll this up into a single Indicator for the whole dashboard to show if Rota B is better or worse for productivity than Rota A (in a variable for example)?

Thanks for any advice/help!

Phil

[sunny_talwar]

I am not entirely sure I understand what you are doing here.... why are we only looking at just 2 rows from the data? Others need not be part of the required output or is this just an example?

Also, when you say whole dashboard to show if Rota B is better or worse.... how exactly do you combine them.... 1 - (Sum(ROTA = B)/Sum(ROTA = A)) or do you avg the individual differences?

[prees959]

Hi,

Thanks for your reply.

I was showing two rows as an example...  I guess I need to average the differences between Rota A and Rota B.  Even if I just compare Grade Type that would be a great start... But if we can show across all dimensions then even better..

Regards,

Phil.

[sunny_talwar]

Is this what you intend to get?

Expression

=Aggr(If([Shift Rota] <> Above([Shift Rota]), Avg([Units Produced])/Above(Avg([Units Produced]))-1), Key, Date)

Where Key is a field created in the script like this

Day&Grade&Dept&[Shift Type] as Key

[prees959]

Hi,  Thanks for replying to this.

This is almost there I think....  Maybe i was too literal with my sample dataset.  Most likely the Shift Rota B wont always come directly after its corresponding Shift A...  The data is probably more fluid.

Could the same formula be used without the Above function?? What I'm trying to say is Compare Key to Key where Key consists of Day + Grade + Dept + Shift Type + Shift Rota ?

Thanks again!

Phil