topic Re: How to calculate combining several columns using restrictions? in QlikView

How to calculate combining several columns using restrictions?

Wed, 08 Feb 2012 15:07:40 GMT

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.

Can anyone please help me with this issue?

Thanks,

Johanna

How to calculate combining several columns using restrictions?

Jason_Michaelides — Wed, 08 Feb 2012 16:45:49 GMT

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?

Wed, 08 Feb 2012 18:30:25 GMT

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?

Wed, 08 Feb 2012 19:12:41 GMT

Something like this?

Re: How to calculate combining several columns using restrictions?

Fri, 10 Feb 2012 12:13:24 GMT

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?

Fri, 10 Feb 2012 18:45:26 GMT

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?

Tue, 14 Feb 2012 10:12:02 GMT

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?

CELAMBARASAN — Tue, 14 Feb 2012 10:59:03 GMT

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