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

# How to write aggr function

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.....

Thanks

• ###### Re: How to write aggr function

You didn't add Fiscal Year as Aggr() dimension? That seems to be part of your chart?

Sum(Aggr(Sum({<[/BIC/A_FVER]={'F00'},SCOPE={'YES'}>}QUANTITY)*Avg({<SCOPE={'YES'}>}[UNIT PRICE]),[MATERIAL CODE],[VENDOR CODE],[PLANT CODE], [FISCAL YEAR]))

• ###### Re: How to write aggr function

U r Genius Bro,thanks for the help