topic Re: Count distinct, or aggr? in QlikView

Count distinct, or aggr?

annabergendal — Sun, 15 Mar 2015 20:22:46 GMT

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!

Re: Count distinct, or aggr?

MK_QSL — Sun, 15 Mar 2015 20:49:18 GMT

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

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

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

Re: Count distinct, or aggr?

annabergendal — Sun, 15 Mar 2015 21:36:32 GMT

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.

Re: Count distinct, or aggr?

sunny_talwar — Mon, 16 Mar 2015 00:28:44 GMT

May be this:

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

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

HTH

Best,

Re: Count distinct, or aggr?

annabergendal — Mon, 16 Mar 2015 14:07:06 GMT

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.