topic Condition in a List Box in QlikView

Condition in a List Box

Mon, 22 Aug 2011 11:15:15 GMT

Hi guys,

Need some urgent help on a requirment.

I am trying to display Cust_Nos that meet only this criteria in a list box or any other kind of object. The criteria is that I want to show only customer numbers that have the sum of expense value of 1 greater than the sum of expense value of 2.

I have entered this into the expression in a list box

=if(sum({<[Expense] = {'1'}>} Value * -1) > sum({<[Expense] = {'2'}>} Value), [Cust_No])

I get a list box with all customer numbers and then a new column with these customer numbers that meet my criteria.

I only want my list box to show the customer numbers that meet my criteria and nothing else.

Can someone help with this?

Thank you

Condition in a List Box

swuehl — Mon, 22 Aug 2011 11:40:51 GMT

I assume you entered above in tab expression of a list box.

If you want to limit your field values, I think you need to use the expression in field selection in tab general.

Go to tab general, open drop down list "Field" and at the very end, there you can select <Expression> and enter any expression.

Regards,

Stefan

Condition in a List Box

Mon, 22 Aug 2011 12:00:49 GMT

Hi Stefan,

When I enter my expression in the General tab --- > Field ---> Expression, the list box does not does not display anything and just comes up with a blank.

Any ideas?

Re: Condition in a List Box

Miguel_Angel_Baeyens — Mon, 22 Aug 2011 12:21:18 GMT

Hi,

You will need to get the actual values, so what about this:

First, you build the list of values that match with the condition of the sum (use a text object to check it)

Chr(39) & Concat(Aggr(Sum({< [Expense] = {'1'} >} Value * -1) > Sum({< [Expense] = {'2'} >} Value), [Cust_No]), Chr(39) & Chr(44) & Chr(39))

Chr(39) and Chr(44) are needed if you expect strings, in order to get them properly quoted: 'A','B','C'...

Then, if the field value matches the above, then show the value, otherwise, don't show anything

=If(Match([Cust_No], $(=Chr(39) & Concat(Aggr(Sum({< [Expense] = {'1'} >} Value * -1) > Sum({< [Expense] = {'2'} >} Value), [Cust_No]), Chr(39) & Chr(44) & Chr(39)) & Chr(39))) > 0, [Cust_No])

The Aggr() stuff is needed to get one result per value, and the Match() compares the actual value with the possible values that match your criteria. Now yo do have a list of values that fit one condition, and they will be shown in your listbox as expected.

I haven't checked the parentheses, so there might be some bugs in the expressions.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Condition in a List Box

swuehl — Mon, 22 Aug 2011 12:28:53 GMT

I think this should work also as calculated expression in the field expression:

=if(aggr(sum({<[Expense] = {'1'}>} Value * -1),[Cust_No]) > aggr(sum({<[Expense] = {'2'}>} Value),[Cust_No]), [Cust_No])

Regards,

Stefan

Condition in a List Box

Mon, 22 Aug 2011 12:51:10 GMT

Thanks a lot guys, both answers are actually correct. I am very grateful and happy.

Thanks a lot