1 Reply Latest reply on Oct 1, 2012 5:23 PM by Tracy Rodgers

    Cumulative Distinct Counts

    Eric Meger

      I have a data set which collects sensor data (messages) from a large number of sensors.  Sensors send anywhere from zero to hundreds of messages per day and not consistently.  The data is stored in an Oracle database with the following relevant fields:

       

      SensorID     Date/TimeSent    

       

       

      I track the the total messages per sensor per day or per hour easily with Tableau.

      What I want to do is to chart the accumulation of SensorIDs over the course of the day.

       

      This means that I need to

      • Do a DCOUNT on SensorID for each hour (no problem).
      • then I need to do a DCOUNT for hours 1 to 2 (which will eliminate duplicates and give me the cumulative Sensor detections.
      • Repeat for each successive hour in the day.
      • Plot the sequential DCOUNTS to see how fast I accumulate,say, 80% of all the sensors. My key goals:

       

           1) To know how long it takes to detect all the sensors

           2) What percent are detected in a day

           3) How fast do we ramp up to near complete detection.

       

      It seems that table calculations could play a role here but I am stuck.

       

      I would appreciate any help.