topic Condition within set analysis expression in QlikView

Condition within set analysis expression

annabergendal — Fri, 19 Dec 2014 07:49:13 GMT

Hi,

I would like to include a condition in my set analysis.

I have the following table, where I would like to count the SubscriptionID's that has Status 7 and a status 2. So I would like to count ID nbr 123 but not 124. How can I do this?

This expression gives me both 123 and 124:

=count({$<Status={"7"}>} distinct SubscriptionID)

Table:

Load * inline

[

SubscriptionID, Status, Date

123, 2, 2014-01-01

123, 6, 2014-02-01

123, 7, 2014-03-01

124, 7, 2014-03-01

125, 2, 2014-01-01

125, 6, 2014-02-01

];

Thanks

Re: Condition within set analysis expression

amit_saini — Fri, 19 Dec 2014 08:01:29 GMT

Try this:

Table:

Load * inline

[

SubscriptionID, Status, Date

123, 2, 2014-01-01

123, 6, 2014-02-01

123, 7, 2014-03-01

124, 7, 2014-03-01

125, 2, 2014-01-01

125, 6, 2014-02-01

]

Where SubscriptionID <> '124' ;

Thanks,
AS

Re: Condition within set analysis expression

sushil353 — Fri, 19 Dec 2014 08:03:47 GMT

Hi,

in the dimension.. you can write condition

if(status = 7 and status =2, subscriptionID)

also check the option for suppress null values..

HTH

sushil

Re: Condition within set analysis expression

sunilkumarqv — Fri, 19 Dec 2014 08:04:25 GMT

Try like this

=count({$<Status={"7"},SubscriptionID={'*'}-{'124'}>} distinct SubscriptionID)

Re: Condition within set analysis expression

alkesh_sharma — Fri, 19 Dec 2014 08:34:52 GMT

count({<Status={'7'},SubscriptionID={'*'}-{'124'}>} DISTINCT SubscriptionID)

Re: Condition within set analysis expression

annabergendal — Fri, 19 Dec 2014 08:37:43 GMT

I just have a textbox where I put the result, so I need to refer to the dimension in the expression...

Re: Condition within set analysis expression

annabergendal — Fri, 19 Dec 2014 08:38:59 GMT

In the real app, I have hundreds of SubscriptionID's, so I can't refer to the number of SubscriptionID like 124. I need to refer to the status 2 somehow...

Re: Condition within set analysis expression

licciardello — Fri, 19 Dec 2014 09:51:43 GMT

(sorry for my english)...

you can create two variables.

like this (about)

=count({$<Status={'$(vStatus)'}, SubscriptionID-={'$(vSID)'}>} distinct SubscriptionID)

You can use two inputbox for insert the value of var

Re: Condition within set analysis expression

manojkulkarni — Fri, 19 Dec 2014 10:13:05 GMT

Use below expression to get subscription id having both status

=count({<[SubscriptionID] = p({<[Status] = {7}>}) * p({<[Status] = {2}>}) >} distinct SubscriptionID)

Re: Condition within set analysis expression

annabergendal — Fri, 19 Dec 2014 10:13:53 GMT

Thanks,

but how can I set the variable vSID to take into account the SubscriptionID's that has Status 2?

Or, have multiple values in vStatus to include both 2 and 7?

Re: Condition within set analysis expression

annabergendal — Fri, 19 Dec 2014 10:17:20 GMT

Thank you!

Exactly what I needed!

Works perfect thanks a lot