Can I use a list box to determine the values of a field restricting a set analysis expression?

[#1]

Hi all,

I know the title of the question isn't the most coherent title you've ever seen so I'll try to explain myself:

I have a straight table with many expressions, some of the expressions take the results of other expressions in the object as inputs.

Let's say I have two tables: one table has account information and various fields for customers that joined a company, and another table - a Churn table - with information about the customers that left the company (such as reason for leaving).

I have expressions running on just the churn table data, with set analysis restricting which types of churn customers I want (e.g. customers that left due to a better offer from a competitor). What I want is to have some more flexibility: have a list box in my model with all the different reasons (I have a field for that, of course), where I could just check the reasons I want to be shown in the model. Can I use a reference to a list box in a set analysis expression? Obviously I want the selections I make in the list box affect just the expressions with the set analysis, and not other parts of the model.

If possible, please explain how.

Unfortunately, due to the sensitivity of the data, I can't give a sample file

Many thanks!

Al

[#2]

Hi,

1) If you want to share an example to the comuunity, you can modify the data by scrambling them: menu / settings / document properties / tab Scrambling in order to randomize parts or all of data. QV will replace the true data or fields by false random data.

2) The data in your table depend on the choice made in the combo box except if you write a set analysis. In other words,

a) if you do nothing like sum(sales), it will depend on the selections made in the combo (what do you want to do then ?)

b) you can set a Field to all with a set (you overwrite the selections made in the combo) : {<Reason=>}

please see a document I have written on that subject : http://community.qlik.com/docs/DOC-4951

Fabrice

[#3]

Hi, thanks for your reply! Merci beaucoup pour votre aide!

1) I did not know about the scrambling, but there's still the small issue of having a 100MB model .

2) I'll explain what I did (which seems to work, maybe not elegantly though) and maybe you could suggest how to achieve it better.

Based on http://community.qlik.com/thread/87102 I added [churn reason]=P([churn reason]) to the set analysis of all churn related expressions, and [churn reason]=, to all other expressions.

Seems to work, although I used it like a dummy - didn't even know P() until seeing that thread (I see it in your PDF as well but don't quite understand it still).

Thanks!@

[jonathandienst]

Hi

If you are using QV11 or greater:

• Add a list box for the reason codes, so the user can select reason codes
• In the expression, use set analysis as you are doing, eg expression1 =

               Sum({<ReasonCode = {'Better Offer'}>} Sales)

• On the expressions tab, enter the following in the conditional box for that expression:

               WildMatch(Concat(ReasonCode), '*Better Offer*')

Now the expression displays if no ReasonCode is selected (displays all reason codes), of if 'Better Offer' is selected in the list (displays only the selected reason codes if something is selected in ReasonCode)

HTH

Jonathan

[#5]

hmm interesting but that wouldn't work for me I think.

can you please better explain what P() does ?

thanks!

[#6]

Hmm... this is strange. I get churn numbers even when nothing is selected in the list box! What am I missing here ? Thanks

[#7]

Al,

P() return the possible values, the ones that can be associated with the other values (QV is an associative model)

E() is the inverse, it returns the excluded values.

Fabrice