How to write a expression

[naveen_kunuru]

Hello All,

                  I need help in writing a expression ,the below table as three dimensions

                                                                                           1)Material Code,2)Vendor code,3)Plant code

        and Four expressions    

                                             1)Residual:    sum({<[FISCAL YEAR]=>}Resi)

                                             2) VolumeFY2017(16-17):  Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)                                                                3)    Column(1)*Column(2)

                                            4)Savings Potential :

sum(aggr(

Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)*sum({<[FISCAL YEAR]=>}Resi])

,[MATERIAL CODE],[VENDOR CODE],[PLANT CODE]

))

So the problem is when i use column(1)*column(2) iam getting 29973.741(203.903*147) which is correct but

when i am trying to use along with aggr function i am getting Zero ,

so kindly any one suggestion what am i missing in my expression 4th, or is there any other way to write the above expression

Thanks

[naveen_kunuru]

        1)Residual:        sum({<[FISCAL YEAR]=>}Resi)

                                             2) VolumeFY2017(16-17):  Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)        

                                            3)    Column(1)*Column(2)

                                            4)Savings Potential :

sum(aggr(

Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)*sum({<[FISCAL YEAR]=>}Resi)

,[MATERIAL CODE],[VENDOR CODE],[PLANT CODE]

[Anil_Babu_Samineni]

But, Column(1) and Column(2) won't written Aggr() then why you are trying with Aggr() in third column?

[naveen_kunuru]

i need to show only column 4 ,i just displayed rest of the column for clear understanding purpose

[tresesco]

Are expression 3 and 4 supposed to return same results? if so, why are you trying with aggr() when you get that using column() function?  Meanwhile, you can try like:

sum(aggr( NODISTINCT

Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)*sum({<[FISCAL YEAR]=>}Resi)

,[MATERIAL CODE],[VENDOR CODE],[PLANT CODE]

))

[naveen_kunuru]

the total which suppose to get from expression 4   is correct (2513340.18+29973.741=2543313.921),

the total i am getting from column(1) * column(2) is wrong ,thats why i am trying to use aggr function to get correct total

[naveen_kunuru]

the above expression giving me wrong total

[naveen_kunuru]

[tresesco]

Could you share your sample app?

[naveen_kunuru]

Any more Suggestion on the above requirement ....