Forum Discussion

EZiamslow's avatar
EZiamslow
Helper III
2 years ago
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 Availabilit...
Need Help
Ahmedx's avatar
Ahmedx
Super User
2 years ago

write what result you expect

EZiamslow's avatar
EZiamslow
Helper III
2 years ago

Ahmedx 

ToolDateTimeAvailabilityCount
AA18/20/22 11:49 PMD1
AA18/20/22 10:08 PMU 
AA18/20/22 2:08 PMU 
AA18/19/22 7:11 PMD1
AA18/18/22 9:06 PMD 
AA18/18/22 8:15 PMU 
AA18/18/22 6:15 PMD1
BB18/20/22 11:08 PMD1
BB18/20/22 10:08 PMD 
BB18/20/22 6:45 PMU 
BB18/20/22 5:12 PMD1
BB18/20/22 2:08 PMU 
BB18/19/22 7:11 PMD1
BB18/18/22 9:06 PMU 
BB18/18/22 8:15 PMU 
BB18/18/22 6:15 PMD1

 

Based on the sample above, I'd like to see AA1 = 3 and BB1 = 4.

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    The logic of the calculation is not yet clear to me.
    tell me why these changes were not counted

    • EZiamslow's avatar
      EZiamslow
      Helper III
      2 years ago

      This is related to tool status up and down.

       

      D = Down

      U = Up

      From the sample data I provided.

       

      ToolDateTimeAvailabilityCount
      AA18/20/22 11:49 PMD1
      AA18/20/22 10:08 PMU 
      AA18/20/22 2:08 PMU 
      AA18/19/22 7:11 PMD1
      AA18/18/22 9:06 PMD 
      AA18/18/22 8:15 PMU 
      AA18/18/22 6:15 PMD1
      BB18/20/22 11:08 PMD1
      BB18/20/22 10:08 PMD 
      BB18/20/22 6:45 PMU 
      BB18/20/22 5:12 PMD1
      BB18/20/22 2:08 PMU 
      BB18/19/22 7:11 PMD1
      BB18/18/22 9:06 PMU 
      BB18/18/22 8:15 PMU 
      BB18/18/22 6:15 PMD1

       

      Any "D" that are together only count as one same for "U".

       

      If I'm looking at AA1, I have 3 times tool down and 2 times tool up.

      As for BB1, I have 4 times tool down and 3 times tool up.

       

      Data is usually added when people give updates so that's why we have multiple rows with D and U together.

       

      It doesn't matter which one I'm counting within the group. I just need to count one if they are together.