topic Re: Calculate Average...... in App Development

Calculate Average......

prees959 — Mon, 26 Nov 2018 12:49:55 GMT

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

Re: Calculate Average......

sunny_talwar — Mon, 26 Nov 2018 13:03:53 GMT

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?

Re: Calculate Average......

skamath1 — Mon, 26 Nov 2018 13:54:32 GMT

Add the following expression in the measure

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

Re: Calculate Average......

prees959 — Mon, 26 Nov 2018 20:27:37 GMT

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

Re: Calculate Average......

sunny_talwar — Mon, 26 Nov 2018 20:41:23 GMT

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

Re: Calculate Average......

prees959 — Wed, 28 Nov 2018 11:42:54 GMT

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

Re: Calculate Average......

sunny_talwar — Wed, 28 Nov 2018 12:22:48 GMT

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?

Re: Calculate Average......

prees959 — Wed, 28 Nov 2018 13:01:06 GMT

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.

Re: Calculate Average......

sunny_talwar — Wed, 28 Nov 2018 13:10:28 GMT

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

Re: Calculate Average......

prees959 — Tue, 04 Dec 2018 18:45:34 GMT

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

Re: Calculate Average......

sunny_talwar — Tue, 04 Dec 2018 19:46:38 GMT

I don't think I follow. May be it is because of my lack of understanding of your actual data.

Re: Calculate Average......

prees959 — Tue, 04 Dec 2018 21:16:13 GMT

Hi,

I've attached a larger sample dataset.

At a point in time a new Rota (Rota B) was introduced. What I am trying to portray is if this new Rota is more or less productive than Rota A.

By taking an average of Rota A and comparing it to Rota B seems too simplistic as there maybe Departments, Grades or particular Days which could prove more or less productive than others.

it would be great to have a single High level KPI that says if Rota A is more or less productive than Rota B and then a be able to drill down into Dept, Grade, Day of week....

I hope this helps?

Phil

I hope this helps

Re: Calculate Average......

sunny_talwar — Tue, 04 Dec 2018 21:24:25 GMT

I think this is a great start. Now would you be able to take this data and provide me with few test scenarios with their output to understand this better?