Application of a formula on vales of different codes are not appeared against each code

[nevilledhamsiri]

Hi, Community

Please look in to my calculation which was done on separate codes by application of a formula in the P/T. The formula used to multiply is  there in the straight table. The issue is once applied, correct amount is shown on the total row but I need the same amounts to be shown against the each code.

Please help

Neville

[dplr-rn · Accepted solution]

Dont understand the business logic completely but the issue is for each individual line the second part (bold below) becomes 0

e.g. account code 76110 second part of your code does not have this code. so it is always 0

i got it working by adding total keyword to the denominator but not sure if thats what you are intending from business logic

(SUM({<ACCOUNT_CODE={'76110','76130','76150','76170','76180','76190','76230','76250','76270','76310','76330','76350','76355','76370','76390','76400','77110','77120','77130','77150','77170','77190','77210','77230','77250','77270','77310','77330','77370','77390','77410','77430','77450','77460','77470','77480','77490','77500','77510','77550','77650','78130','78150','78170','78210','78230','78290','78310','78330','78910','79110','79140','79150','79170','79190','77655','77657','77659','77661','77663','78110','77761','78120','79161'}>}MO_DR-MO_CR))

*((SUM(TOTAL{<ACCOUNT_CODE={'61110','61160','61210','61260','61270','61460','61510','61560','61740','61760'}>}CR-DR))

/((SUM(TOTAL{<ACCOUNT_CODE={'61110','61160','61210','61260','61270','61460','61510','61560','61740','61760'}>}CR-DR))+(SUM(TOTAL{<ACCOUNT_CODE={'61310','61320','61360'}>}CR-DR))))

[nevilledhamsiri]

Hi, Ranjith

This is alright. It serves my requirement. One more issue to be cleared. Once this is done, I need to create a another column (field). That is to add 1 to the last two number of each code & to rename the same field as Motor_code.

Eg- 76110------76111(76110+1=761111), 76130-------76131(76130+1=76131) etc,

How this is achieved

[dplr-rn]

add a  calculated dimension =ACCOUNT_CODE+1

or am i missing something and over simplifying it

[dplr-rn]

or an expression

= ONLY({<ACCOUNT_CODE={'76110','76130','76150','76170','76180','76190','76230','76250','76270','76310','76330','76350','76355','76370','76390','76400','77110','77120','77130','77150','77170','77190','77210','77230','77250','77270','77310','77330','77370','77390','77410','77430','77450','77460','77470','77480','77490','77500','77510','77550','77650','78130','78150','78170','78210','78230','78290','78310','78330','78910','79110','79140','79150','79170','79190','77655','77657','77659','77661','77663','78110','77761','78120','79161'}>}ACCOUNT_CODE)+1

[nevilledhamsiri]

If I am to do it purely on this set of codes, how best it could be arranged?. The syntax for me looks little tough

[dplr-rn]

Which one? motor code?

[nevilledhamsiri]

Yes

[dplr-rn]

Not sure how to answer that.

You can put the ACCOUNT_CODE+1 in the script to generalize it i suppose

[nevilledhamsiri]

= ONLY({<ACCOUNT_CODE={'76110','76130','76150','76170','76180','76190','76230','76250','76270','76310','76330','76350','76355','76370','76390','76400','77110','77120','77130','77150','77170','77190','77210','77230','77250','77270','77310','77330','77370','77390','77410','77430','77450','77460','77470','77480','77490','77500','77510','77550','77650','78130','78150','78170','78210','78230','78290','78310','78330','78910','79110','79140','79150','79170','79190','77655','77657','77659','77661','77663','78110','77761','78120','79161'}>}ACCOUNT_CODE)+1

Re: Application of a formula on vales of different codes are not appeared against each code

Once applied this on the cal_dimen on Account code, no expected results are produced