13 Replies Latest reply: Jan 15, 2018 7:53 AM by Anil Samineni

# How to write a expression

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

• ###### Re: How to write a expression

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]

• ###### Re: How to write a expression

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]

))

• ###### Re: How to write a expression

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

• ###### Re: How to write a expression

the above expression giving me wrong total

• ###### Re: How to write a expression

Could you share your sample app?

• ###### Re: How to write a expression

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

• ###### Re: How to write a expression

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

• ###### Re: How to write a expression

Any more Suggestion on the above requirement ....

• ###### Re: How to write a expression

i managed  to convert the zero value in to actual value by using below expression ,but the total i am getting is wrong

Expression:

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

How to sum  rows

• ###### Re: How to write a expression

What is the wrong here?

• ###### Re: How to write a expression

Total,

It suppose to be sum of (2513340.18+29973.741=2543313.921)

but the total is 5033019.342

• ###### Re: How to write a expression

Finally, The sub total will calculate over Total only for each measure, So for me it looks good, To get rid we need to use Aggr(). Off course, It's tough to say without having something. Will you attach sample?

• ###### Re: How to write a expression

Please try to reply back ASAP or else it may wind out for long