Complications with using AGGR() and FirstSortedValue()

vlad_komarov — Wed, 22 Dec 2021 15:42:54 GMT

Hello all,

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

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.

Re: Complications with using AGGR() and FirstSortedValue()

antoniotiman — Thu, 22 Jun 2017 07:00:05 GMT

Hi Vladimir,

like this

Count(Aggr(Min(Operational_Hour),Site,Date,DeviceID))

Regards,

Antonio

Re: Complications with using AGGR() and FirstSortedValue()

vlad_komarov — Thu, 22 Jun 2017 15:45:52 GMT

Antonio,

Thanks for the suggestion, it works as needed!

Regards,

Vlad

topic Complications with using AGGR() and FirstSortedValue() in App Development

Complications with using AGGR() and FirstSortedValue()

Re: Complications with using AGGR() and FirstSortedValue()

Re: Complications with using AGGR() and FirstSortedValue()