Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Condition in a List Box

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

1 Solution

Accepted Solutions
MVP
MVP

Condition in a List Box

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

5 Replies
MVP
MVP

Condition in a List Box

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

Not applicable

Condition in a List Box

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?

MVP
MVP

Re: Condition in a List Box

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

MVP
MVP

Condition in a List Box

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

Not applicable

Condition in a List Box

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

Thanks a lot

Community Browser