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
