7 Replies Latest reply: Feb 14, 2012 5:59 AM by Celambarasan Adhimulam

# How to calculate combining several columns using restrictions?

Hi,

I am trying to solve a data problem. I need to create a formula that takes the sum (based on column A) of "to X" value minus the corresponding value in "balance" for X. The similar will apply for the "to Y". Another restriction is that variables in column "C" will differ, so there must be a restriction for this as well.

 A B C Balance To X To Y TO ALLOCATE X TO ALLOCATE Y 1 X Z 508.98 12.41 496.57 1 Y Z 12,334.00 300.83 12,033.17 -195.74 195.74 2 X Z 492.59 12.01 480.58 2 Y Z 21,118.62 515.09 20,603.53 34.51 -34.51

The two last columns "TO ALLOCATE X" and "TO ALLOCATE Y" shows the numbers that I would like to achieve.

Thanks,

Johanna

• ###### How to calculate combining several columns using restrictions?

Can you explain again how you achieve your desired result please?  I can't work out how you got to -195.74, for example.

Thanks,

Jason

• ###### How to calculate combining several columns using restrictions?

Hi,

In order to get -195.74 I do 12.41+300.83-508.98. For Y I get 195.74 I do 496.57+12,033.17-12,334.

Thanks,

Johanna

• ###### Re: How to calculate combining several columns using restrictions?

Something like this?

• ###### Re: How to calculate combining several columns using restrictions?

Hi,

Is it possible to send the expression instead? I don’t have a full license (yet), so I cannot open your file.

Thanks!

• ###### Re: How to calculate combining several columns using restrictions?

I use a straight table with "A" as dimension.

To Allocate x: =Sum([To X]) - Sum({<B = {'X'}>}Balance)

To Allocate y: =Sum([To Y]) - Sum({<B = {'Y'}>}Balance)

• ###### Re: How to calculate combining several columns using restrictions?

Hi,

Thanks for this, but the thing is that I need to have all columns that indicated in the first posting. Any ideas how to solve this?

Thanks,

Johanna

• ###### Re: How to calculate combining several columns using restrictions?

Hi,

Try with this expression

To Allocate X =Sum(TOTAL <A> [To X])-Aggr(Sum( {<B={'X'}>}Balance),A)

To Allocate Y=Sum(TOTAL <A> [To X])-Aggr(Sum( {<B={'Y'}>}Balance),A)

Hope it helps

Celambarasan