3 Replies Latest reply: Nov 8, 2017 11:33 PM by naveen kumar RSS

    How to write aggr function

    naveen kumar

      Hello All,

                        Some thing weird is happening  with me ,i need suggestion in resolving this issue plz ,from the below table,

       

      Column 1=Quantity

                        Expression: Sum({<[/BIC/A_FVER]={'F00'}>}QUANTITY)


      Column 2=Price

                        Expression: avg({<SCOPE={'YES'}>}[UNIT PRICE])

       

      Column 3=Quantity * Price

                        Expression: Sum({<[/BIC/A_FVER]={'F00'}>}QUANTITY)*avg({<SCOPE={'YES'}>}[UNIT PRICE])


      Column 4: Same Column 3 expression but I am using aggr function

       

                        Expression: Projected Spend(Quantity * Price)


                            sum(aggr(Sum({<[/BIC/A_FVER]={'F00'},SCOPE={'YES'}>}QUANTITY)*avg({<SCOPE={'YES'}>}[UNIT PRICE]),[MATERIAL CODE],[VENDOR CODE],[PLANT CODE]))


      Instead of getting zero I am getting values for each row when I use aggr function ,I am getting zero for each row if I am not using aggr function ,

          so is something wrong with my 4th expression ,because I suppose to get zero for row instead of same values.....


       

      Capture.PNG



      Thanks