Forum Discussion
Solved
Count every time status changes
Hi,
I need help with calculation every time tool status changes. I have many tool IDs set in the same table similar to the one below. What would be the best way to count where every time Availability column changes.
Sample Data Set:
| Tool | DateTime | Availability | Count |
| AA1 | 8/20/22 11:49 PM | D | 1 |
| AA1 | 8/20/22 10:08 PM | U | |
| AA1 | 8/20/22 2:08 PM | U | |
| AA1 | 8/19/22 7:11 PM | D | 1 |
| AA1 | 8/18/22 9:06 PM | D | |
| AA1 | 8/18/22 8:15 PM | U | |
| AA1 | 8/18/22 6:15 PM | D | 1 |
| BB1 | 8/20/22 11:08 PM | D | 1 |
| BB1 | 8/20/22 10:08 PM | D | |
| BB1 | 8/20/22 6:45 PM | U | |
| BB1 | 8/20/22 5:12 PM | D | 1 |
| BB1 | 8/20/22 2:08 PM | U | |
| BB1 | 8/19/22 7:11 PM | D | 1 |
| BB1 | 8/18/22 9:06 PM | U | |
| BB1 | 8/18/22 8:15 PM | U | |
| BB1 | 8/18/22 6:15 PM | D | 1 |
And try this
8 Replies
write what result you expect
Tool DateTime Availability Count AA1 8/20/22 11:49 PM D 1 AA1 8/20/22 10:08 PM U AA1 8/20/22 2:08 PM U AA1 8/19/22 7:11 PM D 1 AA1 8/18/22 9:06 PM D AA1 8/18/22 8:15 PM U AA1 8/18/22 6:15 PM D 1 BB1 8/20/22 11:08 PM D 1 BB1 8/20/22 10:08 PM D BB1 8/20/22 6:45 PM U BB1 8/20/22 5:12 PM D 1 BB1 8/20/22 2:08 PM U BB1 8/19/22 7:11 PM D 1 BB1 8/18/22 9:06 PM U BB1 8/18/22 8:15 PM U BB1 8/18/22 6:15 PM D 1 Based on the sample above, I'd like to see AA1 = 3 and BB1 = 4.
The logic of the calculation is not yet clear to me.
tell me why these changes were not counted
pls try this
