2 Replies Latest reply: Jun 22, 2017 11:45 AM by Vladimir Komarov RSS

    Complications with using AGGR() and FirstSortedValue()

    Vladimir Komarov

      Hello all,

       

      I've got a data set that shows multiple devices appearing in several departments (on multiple Sites) over time:

      Screen Shot 2017-06-21 at 9.46.33 PM.png

      I need to have a count of these devices per Site and per Date, with the condition that these devices will be counted just once per Day, even if they are reported in several departments (or in one department) on one Site several times during that date.

      In the sample above: device with ID=00034da8085be should be counted just once at 10:00 a.m. and counts for 02:00 and 04:00 p.m. should be just ignored.

       

      I've tried to build a Measure's formula using AGGR and FirstSortedValue functions, but was not very successful doing so.

      I would really appreciate a suggestion how to handle that.

       

      Regards,

      Vlad

      P.S. the sample file is attached.