4 Replies Latest reply: Jan 26, 2015 9:00 AM by Simon Brulotte RSS

    AGGR: return values on one line

    Simon Brulotte

      How do I get values for every line?

      I'm suppose to show for each "Portefeuille" on it's own, the total "Portefeuille" market value.

      The values I get with the expression below are pretty accurate, but they are returned only for one line.

      Total is as follows:

      =sum(aggr(sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_OBL_PORT_NO)} >} VAL_RPT.MKT_VALUE) , VAL_RPT.PORT_CODE))

       

       

      What am I doing wrong?

        • Re: AGGR: return values on one line
          Elena Prandoni

          Hello Simon,

          after dimension Emetteur I see there is another dimension: you can try to make tha aggregation even on this field

          writing sum(aggr(sum(.....), VAL_RPT.PORT_CODE, ThirdDimension)).


          If it is not correct, you had better to attach a sample qvw.

          • Re: AGGR: return values on one line

            Hi Simon,

             

            Try this:

            =sum(aggr(NODISTINCT sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_OBL_PORT_NO)} >} VAL_RPT.MKT_VALUE) , VAL_RPT.PORT_CODE))


            NODISTINCT  should return a value for every record.


            Best,


            Matt

            • Re: AGGR: return values on one line
              neetha P

              Hi,

               

              =sum(aggr(sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_OBL_PORT_NO)} >} VAL_RPT.MKT_VALUE) , VAL_RPT.PORT_CODE))

               

              the above expression is grouping on VAL_RPT.PORT_CODE,that's the reason its returning only for one line.

              by default it will display for first record in the group.

               

              Regards

              Neetha

                • Re: AGGR: return values on one line
                  Simon Brulotte

                  Elena: there were dimensions, and I had tried taking them out before posting this, with no avail. I really need that figure to show the grouping on PORT_CODE...

                   

                  Matthew: NODISTINCT returns a value for each line, but are insanely high figures that I can't comprehend :/

                  BUT, it was the right answer after all, I just needed to take out the sum preceding the expression:

                   

                  =aggr(NODISTINCT sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_OBL_PORT_NO)} >} VAL_RPT.MKT_VALUE) , VAL_RPT.PORT_CODE)

                   

                   

                  THANK YOU ALL!!!!!

                  I love this community.