Condition in a List Box

[#1]

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

[swuehl]

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

[#3]

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?

[Miguel_Angel_Baeyens]

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

[swuehl · Accepted solution]

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

[#6]

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

Thanks a lot