Hi, I've the following data. I want to find out the Max, Min and Avg for individual car when the status change. Example, how days in term of MAX, Min and Avg IN/#IN before the status change to OUT/#OUT. This apply to OUT change to IN. # is assessed which i need to count them too.
Example based on the sample data below.
Car A
Max IN = 3.5 days (is the bold below)
Min IN = 0.5 days
Max OUT = 3.5 days
Min OUT = 0.5 days
Date | Car | AM | PM |
1/1/2020 | A | IN | IN |
2/1/2020 | A | IN | IN |
3/1/2020 | A | OUT | OUT |
4/1/2020 | A | IN | OUT |
5/1/2020 | A | OUT | OUT |
6/1/2020 | B | OUT | OUT |
6/1/2020 | A | IN | IN |
7/1/2020 | A | IN | IN |
8/1/2020 | A | #IN | #IN |
9/1/2020 | A | IN | OUT |
10/1/2020 | B | IN | IN |
10/1/2020 | A | #OUT | OUT |
11/1/2020 | B | IN | IN |
See the manual work out column for the counts. I want it to auto count in QilkSense KPI.
Date | Car | AM | PM | Manual work out |
1/1/2020 | A | IN | IN | 2 days IN (0.5 x 4) |
2/1/2020 | A | IN | IN |
3/1/2020 | A | OUT | OUT | 1 day OUT |
4/1/2020 | A | IN | OUT | 0.5 IN 0.5 OUT |
5/1/2020 | A | OUT | OUT | 2 days OUT |
6/1/2020 | B | OUT | OUT |
6/1/2020 | A | IN | IN | 3.5 days IN 0.5 day OUT |
7/1/2020 | A | IN | IN |
8/1/2020 | A | #IN | #IN |
9/1/2020 | A | IN | OUT |
10/1/2020 | B | IN | IN | |
10/1/2020 | A | #OUT | OUT | |
11/1/2020 | B | IN | IN | |
Those are sample data. My data consist of other cars in the same table. Please help me. Thank you