Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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?
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.
BI Consultant
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
Thanks a lot guys, both answers are actually correct. I am very grateful and happy.
Thanks a lot