Condition within set analysis expression

[annabergendal]

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

[amit_saini]

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

[sushil353]

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

[sunilkumarqv]

Try like this

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

[alkesh_sharma]

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

[annabergendal]

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

[annabergendal]

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...

[licciardello]

(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

[manojkulkarni · Accepted solution]

Use below expression to get subscription id having both status

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

[annabergendal]

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?