Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

vlad_komarov
Valued Contributor III

Complications with using AGGR() and FirstSortedValue()

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.

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

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

Hi Vladimir,

like this

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

Regards,

Antonio

2 Replies
antoniotiman
Honored Contributor III

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

Hi Vladimir,

like this

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

Regards,

Antonio

vlad_komarov
Valued Contributor III

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

Antonio,

Thanks for the suggestion, it works as needed!

Regards,

Vlad

Community Browser