topic Re: Set analysis in QlikView

Set analysis

annabergendal — Wed, 03 Dec 2014 15:59:17 GMT

Hi,

I have a set analysis that is working fine, it counts Produkt where service_activity.action has a certain number.

With this result I get several result for some produkts, but I only want to know distinct (and it doesn't work to put distinct before Produkt in this expression as I will miss some Produkts)

With current calculation I get for example

5 for ProduktA

3 for ProduktB

1 for ProduktC

=
count({$ <service_activity.action = {"2"} , productsubscription.subscriptionstate -= {"INACTIVE"}>} Produkt)
+
count( {$ < service_activity.action = {"7"}, productsubscription.subscriptionstate -= {"INACTIVE"}>} Produkt)
-
count( {$< service_activity.action = {"5"}, productsubscription.subscriptionstate -= {"INACTIVE"}>} Produkt)

Any ideas on how I can develop my set expression?

Thanks.

Re: Set analysis

marcus_malinow — Wed, 03 Dec 2014 16:10:03 GMT

sum(

aggr(

count({$<service_activity.action={'2'}, productsubscription.subscriptionstate-={'INACTIVE'}>} DISTINCT Produkt)

count({$<service_activity.action={'7'}, productsubscription.subscriptionstate-={'INACTIVE'}>} DISTINCT Produkt)

count({$<service_activity.action={'5'}. productsubscription.subscriptionstate-={'INACTIVE'}>} DISTINCT Product)

, Produkt)

)

Re: Set analysis

annabergendal — Wed, 03 Dec 2014 19:29:04 GMT

With distinct before Produkt, I miss some Produkts, as the sum will be for example 1+0-1=0, when it without distinct would be 4+0-1=3. When positive I need it to be 1. If you understand what I mean... So I need to do a distinct after the calculation somehow...