topic sum if help in QlikView

sum if help

Fri, 19 Sep 2014 12:01:00 GMT

I am trying to write a "sumifs" expression that takes into account criteria from the same table....

Steve

Table	Name	Time
Task	Dave	01:00
Task	Dave	00:30
Task	Dave	02:30
Task	Alan	01:30
Task	Alan	00:30
Full Time Hours	Dave	08:00
Full Time Hours	Alan	08:00
Full Time Hours	08:00

I'm creating a pie chart that has the Task data added up as one slice, then the (Full time hours - task data added up) - this will give me the amount of time where tasks have not been completed.

My issue is that I do not want to include "Steve" in the "non task" time as he is not related to the task information.

Many thanks for your help

Re: sum if help

sundarakumar — Sun, 21 Sep 2014 05:29:36 GMT

Could not understand ur requirement.. Pls clarify.

Re: Re: sum if help

Mon, 22 Sep 2014 07:57:46 GMT

I'm trying to replicate something similar to the above.

My if statement needs to add up the task time for individuals but only take into account the Full time hours if they have completed tasks that day.

Sorry, in my original post the final line should read Full Time Hours , Steve , 08:00

I need a statement that is something like:

IF NAME has completed a "Task", (ADD up Their "Full Time Hours") - (TOTAL of "Task Duration")

Re: Re: sum if help

sundarakumar — Mon, 22 Sep 2014 08:06:30 GMT

From my understanding u want a pie to be shown with split up for task completed and task incomplete..

now u want a split up in task incomplete with who all are the defaulters. for this i would recomendto create a new field in the back end like

if(time=8, 'Full time', name) as name_ful_part

this new field "name_full_part" will return as Full time if it is complete and it will consider name if it is part time. in pie it will show up as desired.

This can even be done with a calculated dimension which is less Prefered. something like

if(time=8,'Ful;l time',name) in the calculated dimension.

hope this helps..

-Sundar

Re: sum if help

Mon, 22 Sep 2014 08:16:20 GMT

hmm.... if i understand this correctly. you have 2 table, one stores the total hours a person need to complete all the tasks. another is the hours the person spend on each task.

so it does make sense if you want to display how much time a person already spend compare to the total he have. for example, Alan already spend 40% of his total time. but summing everyone's time together and display in one pie chart sounds a bit wired.... unless they all share the same Tasks. For example, Alan and Dave share a job which need to complete in 20 hour. Alan already completed 20% and Dave completed 35%. if this is the case, your table structure will have to change.....

Re: sum if help

Mon, 22 Sep 2014 08:22:33 GMT

I don't think I have explained my problem very well.

I have a list of staff's full time employment hours. I also have a data input from a system that records when work is being done.

What I am trying to find out is how much of the staff's working day is captured in this system.

I.e I am employed for 8 hours a day - my work complete on the system adds up to 4 hours, therefore I would show a pie chart of 50% work completed, 50% unknown.

The issue is that I have the working hours for all staff and not all staff will be using the system at the same time - I therefore only want to take into account employees that have used the system over the time period specified.

I might need a variable field in my table that says if this date is related to the current selections and the employee has captured work on the system, I need to add up their full time hours.

Does this make sense yet?

Re: sum if help

Mon, 22 Sep 2014 09:05:53 GMT

oh... so sorry. i was not reading your questions.... so You want to hide "Steve" who don't have "Task".... I think you can add a condition in your expression If(Count(DISTINCT TaskId) <=0, NULL(), xxxx) and in the Presentation tab, check "Suppress Missing".

Re: sum if help

Mon, 22 Sep 2014 12:51:42 GMT

In the end I added an apply map lookup within the script - thank you for your help