How can I achieve this? - prevent double counting

[satiyabaazi]

I have this in my report -
Field 0   Field 1   Field 2   Field 3
Emp 1   a           x1            x2
Emp 1   b           x               x2
Emp 1   c           x1             x2
Emp 1   d           x1            x2
Emp 2   e           y1            y2
Emp 2   f           y1            y2
Emp 2   g           y1            y2

I want to be able to achieve this -

Field 0   Field 1   Field 2   Field 3   Field 4
                a           x1           x2   
                b           x1           x2   
                c           x1           x2   
                d           x1           x2   
Emp 1   a+b+c+d   x1           x2   (x2-x1-(a+b+c+d))
                e           y1           y2   
                f           y1           y2   
Emp 2   e+f+g   y1           y2   (y2-y1 + (e+f+g))

The problem I'm facing is that in my summary row - it adds up (x2-x1) multiple times and I can't figure out a formula that will take into account the count of rows and divide the (x2-x1)/count(number of rows for unique Emp) and then subtract the (a+b+c+d) from it. Any help will be appreciated. Thanks guys!

[stan.parker]

Can you give more information about the data? Do all of the fields reside in the same query subject or do Field 2 and Field 3 company from separate query subjects?

[satiyabaazi]

Field 2 and 3 are from different query subjects - Field 2 is the beginning value, Field 3 is the month end value and the (a+b+c) are the different transactions for the user.

[stan.parker]

Can you explain which fields are in each query subject


Sent from my iPhone using Tapatalk

[satiyabaazi]

Field 0 is the employee name
Field 1 is the addition/subtraction to Field 2 (beginning state) to obtain the values in Field 3 (ending state)
I want a summary row against each employee. Let's assume this example

Emp 1 has beginning state as 100$, the different additions to be considered are +5,-5,+10,-6 and ending state is 115$
So I want to find the value of the calculated variable that is 115-100+(5-5+10-6) = 19
I want this 19 number in my Field 4

Now I'm getting
115-100 + 5 = 20
115-100-5 = 10
115-100 + 10 = 25
115-100 - 6 = 9

and Field 4 becomes 20+10+25+9 = 63 = 19 + 45 (the extra 115-100 three times)

I hope I'm explaining it properly, I just can't seem to incorporate this in Query Studio. Thanks for you response!