2 Replies Latest reply on Jan 13, 2017 3:15 AM by fmpdude

    Return Number of Occurrences between 2 events

    g.bathurst

      Hello,

       

      To simplify I have a Table with 3 fields:

       

      Unit_Serial_Number

      Date_Time_UTC_Adjusted

      L10_Action

       

      As shown in Table View, I want to count the number of records between "Batch Out" on a on going basis (17 as shown) within the table.... I'm stuck on how best to do this....

       

      Thanks for any assistance.

       

      G

        • 1. Re: Return Number of Occurrences between 2 events
          Johan Hedman

          Create a Calculated field called BatchCount with calculation

           

          Case(

          IsEmpty( L10_Action ) ; 0;

          1

          )

           

          Then create a Statistic Field that do sum om that new field.

           

          This will give you numbers

          • 2. Re: Return Number of Occurrences between 2 events
            fmpdude

            Isn't this a database design issue?

             

            I can't really be sure from your brief posting, but why not simply have something like a parent table with the serial number and a key generated for each group of events.

             

            So if your serial number for a batch is M012345, then you could append another key value as a "batch number" like:
            M012345_1.  Then, have a child table to hold the actual events whose foreign key points back to the parent's M012345_1 value.

             

            Then, between each set of "events" for the given serial number + key in the parent table, getting the count is trivial.

             

            You would then just need a way to increment the key in the parent for each new batch.

             

            That seems like a better "database way" to do what you want.

             

            Does that make sense?