5 Replies Latest reply: Aug 22, 2011 8:51 AM by Razor Ramon RSS

    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

        • Condition in a List Box
          Stefan Wühl

          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

              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 de Arce

                  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
                    Stefan Wühl

                    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

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

                   

                  Thanks a lot