Jump to content
We've recently updated our Privacy Statement, available here. ×

Sum value for same id found in multiple columns


Eliza Bayliss

Recommended Posts

UPDATE: SEE ATTACHED WORD DOC FOR A BETTER EXPLANATION OF WHAT I AM TRYING TO ACHIEVE

 

I have a set of employee data where i am trying to get two different totals. The first total is the sum ofactivity minutes for each "ACT_OWNER". The second is the sum of the close minutes for each "CLOSE_OWNER". I can sum those easily as individual totals. The final number i am trying to get is the total minutes for each employee from both calculations.

I attached an imaged of my crosstable. The three calculations i am using for the columns are below. I know the bold underlined one is wrong but dont know what to do to fix it. because i have two columns that represent the employee i cant add both to the crosstable.

Sum(Distinct Min([MTD_ACTIVITY_MINUTES]) OVER (Intersect([sR_OWNER]))) as [Total Activity Minutes],

Sum(Distinct Min([MTD_CLOSE_MINUTES]) OVER (Intersect([ACT_OWNER]))) as [Total Close Minutes],

Sum(Sum(Distinct Min([MTD_ACTIVITY_MINUTES]) OVER (Intersect([sR_OWNER]))),Sum(Distinct Min([MTD_CLOSE_MINUTES]) OVER (Intersect([ACT_OWNER])))) as [Total Productive Minutes]

Sample data is attached as well as the image of my crosstable.

Link to comment
Share on other sites

Hi Eliza,

I think I understand what you're doing but don't think you need to go to the complexity of the OVER And INTERSECTs.

Can you just do this

 

The expression I'm using on the horizontal axis is just:

Sum([CLOSE_MINUTES]) as [Close Minutes], Sum([ACTIVITY_MINUTES]) as [Activity Minutes], Sum([CLOSE_MINUTES]) + Sum([ACTIVITY_MINUTES]) as [Total]

You shouldn't need to use the INTERSECT methodin a cross table like this - the point of the INTERSECT method is to calculate the aggregation wherever the pairs of values in two (or more) intersect. You can think of the INTERSECT method as what Spotfire uses internally to calculate the values of each of the cells in a cross table. I.e. where do the values of the horizontal and vertical axes intersect.

Of course, if I've misunderstood your question, please post a comment explaining how and I'll gladly comment further!

Andrew Berridge (TIBCO Data Science)

View my book on Spotfire!

Link to comment
Share on other sites

Hi Eliza,So is my screenshot correct It looks it to me. I calculate the close minutes as 3019.4. I still think you're over-complicating it. The key to my example is using column names for the measure axis - that allows you to use different aggregations and expressions for each set of values you're displaying.You have chosen ACT_OWNER for the row axis in the cross table. Are you looking at using multiple columns for this You can do it but then they'll be nested. You could also create a hierarchy.I don't think you should be doing all the Min, OVER, intersect stuff. I just don't think it's necessary! Maybe I'm still misunderstanding things. If you need to sum up differently for the activity owner or the close owner, maybe you could provide two cross tables  Or maybe even consider Unpivoting the data somehow so you can easily sum it by activity per employee. One column for employee, another for activity and another for minutes.The cross table I produced shows for each employee, the total minutes for ACT owner, Close minutes, but it's categorised by ACT_OWNER. Because it's a little difficult to understand, could you create a simple table in Word or similar that shows the values/display you want to showAndrew
Link to comment
Share on other sites

Hi Eliza,Great! Thanks for sending the word doc over. I'll consider it carefully next week. One question - why are you using distinct Sum(Distinct [MTD_CLOSE_MINUTES])  will sum the distinct values of MTD_CLOSE_MINUTES, not a single sum for each employee, which what I suspect you wantedThat's my first question after a summary glance over your word doc.As said, I'll study the document carefully and get back to you.Andrew
Link to comment
Share on other sites

I am using distinct because in the data set there is duplicate rows based on the nature of the close data versus the activity data. the MTD values repeat but i really only care about the one number under the employee.  i added distinct because it helps me not add up the same number over and over on those duplicate records.With that being said, i am definetly not an expert so i mau be over complicating things. it seemed to work for me so i went with it.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...