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!
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?
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.
Can you explain which fields are in each query subject
Sent from my iPhone using Tapatalk
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!