Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Add the following expression in the measure
Avg( {$<[Shift Type] = {"Day"} >} [Units Produced])
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
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
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.
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
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