If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How can I achieve this? - prevent double counting

Started by satiyabaazi, 04 Nov 2016 12:29:36 PM

Previous topic - Next topic

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

#2
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!