sum if help

[#1]

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

[sundarakumar]

Could not understand ur requirement.. Pls clarify.

[#3]

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

[sundarakumar]

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

[#5]

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.....

[#6]

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?

[#7]

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".

[#8]

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