Count distinct, or aggr?

[annabergendal]

Hi,

in my app I have a number of transactions of services being activated/deactivated, and I want to calculate the net number of activations, but some services have been registered several times as being activated. Then I only want to count it as one. How can I do this? In my example I want the calculation to be 2 instead of 4. Hopefully I have not simplified this too much, the real world is always more complex

Thanks!

[MK_QSL]

=COUNT({<Activated = {1}, Service = e({<Deactivated = {1}>}Service)>}DISTINCT Service)

or

=COUNT({<Activated = {1}>}DISTINCT Service)

-

COUNT({<Deactivated = {1}>}DISTINCT Service)

[annabergendal]

Thanks a lot,

but trying that I realize I simplified too much.

I added another column which complicates it a bit more.

I want Service A to be calculated as 1, as one subservice is still active. Then this calculation is wrong as it gives 0 for Service A.

[sunny_talwar]

May be this:

=Count(Aggr(Count({<Activated = {1}>}DISTINCT Subservice), Service, Subservice)) -

Count(Aggr(Count({<Deactivated = {1}>}DISTINCT Subservice), Service, Subservice))

HTH

Best,

S

[annabergendal · Accepted solution]

I managed to solve it myself, don't know if it was the most optimal solution, but it works

I skipped column "Deactivated" and used -1 in "Activated" instead, and summarized the total aggregated per service, giving -1 if <0 and 1 if >0.