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: 
heij1421
Partner - Contributor III
Partner - Contributor III

Need help with a formula

Hi All,

Can someone please help me with a formula?

I have 2 tables

Hourentry:
LOAD * INLINE [
Project, Employee, Hours
A, Gerton, 8
A, Jeffrey, 6
A, Kim, 1
B, Jeffrey, 4
B, Mike, 5
]
;

Project:
LOAD * INLINE [
Project, Projectmanager
A, Gerton
B, Mike
]
;


Now I want a table with the following result

   

EmployeeProjectHours totalHours from others on my project
GertonA87
JeffreyA60
JeffreyB40
KimA10
Mike B51
Total-24

8

Especially the last column I find difficult:

- It should only display the hours on lines where the Employee is the project manager

- It should sum all hours for that specific project, except for the project manager himself

- Employee and project are the dimensions

- It should display the totals on the last row

How do I do that in Qlikview?

Thanks for helping,

Bart

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Bart,

here you go - see attached.

The "basic" expression that covers your logic should be:

if(Employee = Projectmanager, sum(total <Project>Hours) - sum(Hours), 0)

Only for Project Managers, get the total of Hours by Project and subtract the Hours spent by the Project manager himself.

However, this formula can only work at the detailed level (by Employee and Project). The total for this expression would be zero. So, it needs to be enhanced with the Advanced Aggregation function AGGR. This way, the results will be pre-calculated at the detailed level and then summarized for the total:

sum(aggr(

    if(Employee = Projectmanager, sum(total <Project>Hours) - sum(Hours), 0)

, Employee, Project))

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

View solution in original post

1 Reply
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Bart,

here you go - see attached.

The "basic" expression that covers your logic should be:

if(Employee = Projectmanager, sum(total <Project>Hours) - sum(Hours), 0)

Only for Project Managers, get the total of Hours by Project and subtract the Hours spent by the Project manager himself.

However, this formula can only work at the detailed level (by Employee and Project). The total for this expression would be zero. So, it needs to be enhanced with the Advanced Aggregation function AGGR. This way, the results will be pre-calculated at the detailed level and then summarized for the total:

sum(aggr(

    if(Employee = Projectmanager, sum(total <Project>Hours) - sum(Hours), 0)

, Employee, Project))

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense