Specifying and excluding values within a field

[#1]

In my qlikview file, I am trying to calculate hours by a developer based on an ID Number for that assigned task. The problem with someone of the ID Numbers is that two different developers have contributed to one task so I can not get the individual amount of hours for the developer that is currently working on it. I have created two excel sheets that are linked together by the field [ID Number] and a qlikview document with the charts and buttons I am currently using, to see what I mean visually.

Basically my end result is to make ID Number 20022 only counts the developer (Sumeet's) hours and ID Number 23580 only counts the developer (Amita's) hours, so that they have the correct number. The other developer that has contributed to the task is not included in our counts because they are not currently working on that task anymore.

If you have any questions please let me know. I hope someone can help me out!

[sunny_talwar]

Check now

Variable definition updates

1) vActualEffort

Sum({$<[Tracker.Category] -= {Research, Estimation}>}If(Developer.Developer = Tracker.Developer,   [Tracker.Time Reported Actual]))

2) vBAHours

Sum({$<[Tracker.Category]={'Research','Estimation'}>} If(Developer.Developer = Tracker.Developer,  [Tracker.Time Reported Actual]))

[#3]

It works in my sample data, but unfortunately not in my real data. I got really large numbers.

I was able to switch the expression around to get the right answers, except for the two that have two developers. The ID Number or WR # 20022 and 23580 are blank ( - ). This also prevents the actual effort and the actual total hours to be calculated which is important.

My variables are now:

vActualEffort - (If([CapGemini.Capgemini Developer] = [Time Tracker.User Full Name], Sum({$<[Time Tracker.Category Description] -= {'Research', 'Estimation'}>} [Time Tracker.Time Reported Actual])))

vBAHours - If([CapGemini.Capgemini Developer] = [Time Tracker.User Full Name], Sum({$<[Time Tracker.Category Description]={'Research','Estimation'}>}  [Time Tracker.Time Reported Actual]))

[sunny_talwar]

So below is the snapshot which isn't working? Also I added the if statement within the Sum function and I see you added if outside Sum. Not sure if your if statement is there for another reason?

[#5]

They both did not work. When I tried using the variables you gave me, it gave me large numbers like in the first screenshot.

[sunny_talwar]

Will you be able to provide a sample where you seeing large numbers?

[#7]

Here is a sample with the real data and a copy of the excel sheet I am using. Hope this helps!

[sunny_talwar]

I am getting an error when I try to open the document. Is there a section access in the application?

[#9]

Sorry about that. You should be good now

[#10]

Also to mention: the bar graph is incorrect. In order to see the true values of the bar graph, you have to click on the OTT Users Button. In order to see the correct values for the chart, you have to click on the OTT Users Button and then on the individual developer's button.