Sum value for same id found in multiple columns

[Eliza Bayliss]

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.

[Andrew Berridge]

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!

[Andrew Berridge]

Just thinking - I notice my values do match yours, but I don't understand where/why they're wrong... Can you elaborate

[Andrew Berridge]

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

[Eliza Bayliss]

Hi Andrew. I wrote out a detailed descritpion that will hopefullymake it clear on what i am trying to do. I will attach the word doc to the original question so you can check it out

[Andrew Berridge]

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

[Eliza Bayliss]

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.

[Andrew Berridge]

OK - thanks for the update. It's an interesting question!Andrew