topic Re: Specifying and excluding values within a field in QlikView

Specifying and excluding values within a field

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Specifying and excluding values within a field

sunny_talwar — Wed, 13 Jul 2016 16:08:39 GMT

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

Re: Specifying and excluding values within a field

Wed, 13 Jul 2016 16:18:00 GMT

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

Re: Specifying and excluding values within a field

sunny_talwar — Wed, 13 Jul 2016 16:40:52 GMT

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?

Re: Specifying and excluding values within a field

Wed, 13 Jul 2016 16:44:42 GMT

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

Re: Specifying and excluding values within a field

sunny_talwar — Wed, 13 Jul 2016 17:16:33 GMT

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

Re: Specifying and excluding values within a field

Thu, 14 Jul 2016 13:06:10 GMT

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

Re: Specifying and excluding values within a field

sunny_talwar — Thu, 14 Jul 2016 13:32:00 GMT

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

Re: Specifying and excluding values within a field

Thu, 14 Jul 2016 15:37:12 GMT

Sorry about that. You should be good now

Re: Specifying and excluding values within a field

Thu, 14 Jul 2016 16:45:16 GMT

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.

Re: Specifying and excluding values within a field

sunny_talwar — Thu, 14 Jul 2016 16:46:07 GMT

Try it out now

Re: Specifying and excluding values within a field

Thu, 14 Jul 2016 18:14:09 GMT

Unfortunately those numbers aren't correct.

The BA Hours for WR #20022 are suppose to be 102 (Research)

Actual Effort should be 24 (Development (11) and Requirements (13))

The BA Hours for WR #23580 are suppose to be 32 (Research).

The reason why these are calculating wrong is because for although Amita is currently assigned to and is working on WR #23580, Sumeet has 22 hours of Research or BA Hours put towards the same WR. For WR #20022, Sumeet is assigned to and is working on it, but Amita has 20 hours of Research and 2 hours of requirements. I need to find a way to segregate these and be able to specify that I only want Amita's or Sumeet's hours because this will happen again in the future.

Re: Specifying and excluding values within a field

Thu, 14 Jul 2016 18:24:50 GMT

Re: Specifying and excluding values within a field

sunny_talwar — Thu, 14 Jul 2016 19:27:28 GMT

What about this now. I apologize for taking this long

Re: Specifying and excluding values within a field

Thu, 14 Jul 2016 20:19:05 GMT

Thank you so much it works great!! You have no idea how helpful this is!