Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

sum if help

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

Steve
TableNameTime
Task

Dave

01:00
TaskDave00:30
TaskDave02:30
TaskAlan01:30
TaskAlan00:30
Full Time HoursDave08:00
Full Time HoursAlan08:00
Full Time Hours08: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

Tags (5)
7 Replies
sundarakumar
Valued Contributor II

Re: sum if help

Could not understand ur requirement.. Pls clarify.

Not applicable

Re: Re: sum if help

Picture2.png
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
Valued Contributor II

Re: Re: sum if help

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

Not applicable

Re: sum if help

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

Not applicable

Re: sum if help

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?

Not applicable

Re: sum if help

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

Not applicable

Re: sum if help

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