Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Specifying and excluding values within a field

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!

1 Solution

Accepted Solutions
sunny_talwar

What about this now. I apologize for taking this long

Capture.PNG

View solution in original post

14 Replies
sunny_talwar

Check now

Capture.PNG

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]))

Not applicable
Author

error.png

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]))

error.png

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?

Not applicable
Author

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?

Not applicable
Author

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?

Not applicable
Author

Sorry about that. You should be good now

Not applicable
Author

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.