6 Replies Latest reply: Feb 15, 2013 8:20 AM by sbruckert RSS

    Conditional sum

      Hi everybody.


      I've got an issue wih a conditional sum where I want to sum a chosen field dependong on the value of another field.


      basically, I have the kind of 2 following table :


      TABLE 1


      Customer IDFLAG






      TABLE 2

      DOC IDCustomer IDVR1VR2VR3VR4


      I've tried that :


      sum( $(FLAG))


      but it doesn't work.


      Does one of you have an idea ?



        • Re: Conditional sum


          you can do conditional SUM in this way:


          Sum ( {$<[Cumstemer ID]={'A'}>} FLAG)


          In this way you're going to Sum the field FLAG only for CUSTOMER ID = 'A'


          Hope it helps



            • Re: Conditional sum

              Thank you for your quick answer.


              I need to give futher details.


              The resulting expression will be used in a pivot table with one of the dimension beeing the customer ID, in order to have the sum of the appropriate field with respect to the customer id for all customers.


              So with your expression, I need to have one expression for each customer which is not what I need.

            • Re: Conditional sum
              Stefan Wühl

              You can try using a CROSSTABLE LOAD on your table2, something like


              CROSSTABLE (FLAG, VALUE, 2) LOAD [DOC ID], [Customer ID], VR1, VR2, VR3, VR4 resident TABLE2;


              drop table TABLE2;


              And maybe create a Key from Customer ID and FLAG to avoid the synth key.

              • Re: Conditional sum

                The resulting pivot table will have the customer ID as 1st dimension and months as 2nd dimension.


                I could not have a cross table because normally, at the end I should have one value (VR1 to VR4) associated with one customer ID.


                Currently, one solution is the following expression, but it's not really smart:


                =if(Flag='VR4', sum( {$<DO_TYPE={'5','6'},[Ligne HT]=-{'0'}>} if([Entete Date Livraison]<='14/02/2013',VR1,VR4)),

                if(Flag='VR1', sum( {$<DO_TYPE={'5','6'},[Ligne HT]=-{'0'}>} VR1),

                if(Flag='VR2', sum( {$<DO_TYPE={'5','6'},[Ligne HT]=-{'0'}>} if([Entete Date Livraison]<='14/02/2013',VR1,VR2)),

                if(Flag='VR3', sum( {$<DO_TYPE={'5','6'},[Ligne HT]=-{'0'}>} if([Entete Date Livraison]<='14/02/2013',VR1,VR3))))))