Using sum and criteria

[hhajjali]

Hello,

I need your help, I am new to qlik sense, and I hope you may have the answer.

Bellow you may find the relation between the tables I have loaded:

A parent task could have multiple sub tasks of multiple type.

Sub task have 3 types: A, B & C

Sub task can be performed by multiple user, each user will have it own spent time.

When i select a user in qlick sense, I can get the sum of spent time on sub task of type A by using the following:

Sum({<[Type]={A}>}TimeSpent) or sum(if(Type='A',TimeSpent).

I am searching to know how to get the sum of spent time by all other users out the my selection, on all sub tasks of type A of all parent task where my selected user has spent time on.

Example:

Parent Task 1

       SUB TASK 1 – type A

                       USER1, time spent 5

                       USER2, time spent 6

       SUB TASK 1 – B

                      USER1, time spent 1

                      USER2, time spent 1

Parent Task 2

       SUB TASK 1 – A

                        USER1,time spent 5

                        USER3,time spent 5

       Sub TASK 2 – A

                        USER2, time spent 6

                        USER3, time spent 4

Parent Task 3

       SUB TASK 1 – A

                        USER5,time spent 1

                        USER3,time spent 5

       Sub TASK 2 – A

                        USER2, time spent 6

                        USER3, time spent 4

Sum({<[Type]={A}>}TimeSpent) will give me: 10

I am searching the expression that can give me the  sum of the green values (21), and not those in red or black: USER1 has worked only on Parent Task 1 and 2 and I need the sum of time spent of on sub tasks type A of Parent task 1 & 2.

Thanks in advance for your help.

[rubenmarin]

Hi Haj, you can use the E() function in set analysis, wich represent excluded values:

Sum({<[Type]={A}, UserField=E(UserField)>}TimeSpent)

[hhajjali]

Thanks Ruben, it help me go a step forward, but Sum({<[Type]={A}, DisplayName=E(DisplayName)>}TimeSpent , it gives the sum of spent time of all other users on sub task of type A. But it does not take into consideration to select only the parent tasks of which my user has spent time.

I am also trying to see how may I use P function.

Any other ideas?

[rubenmarin]

Hi again, I didn't tested but maybe:

Sum({<[Type]={A}, DisplayName=E(DisplayName), TaskField=P(DisplayName)>}TimeSpent)

Edit: The P() function returns possible value, and maybe it should be:

Sum({<[Type]={A}, DisplayName=E(DisplayName), TaskField=P(TaskField)>}TimeSpent)

[hhajjali]

Hello again,

One more step forward

Sum({<[Type]={A}, DisplayName=E(DisplayName), key=P(key)>}TimeSpent it gives the sum of all time spent on sub task of type A for all tasks where the selected user worked on.

For example if the user worked only on a sub task type B in a task. I do not want that the sum include time passed on type A sub task of this parent task. But this expression take it into account.

I expand my previous example: in the data bellow i need to extract only the sum of green values.

Sum({<[Type]={A}, DisplayName=E(DisplayName), key=P(key)>}TimeSpent will give me the sum of green values +1 +4  (time spent parent task4, sub task 1 A).

Parent Task 1

      SUB TASK 1 – type A

                      USER1, time spent 5

                      USER2, time spent 6

      SUB TASK 1 – B

                      USER1, time spent 1

                      USER2, time spent 1

Parent Task 2

      SUB TASK 1 – A

                        USER1,time spent 5

                        USER3,time spent 5

      Sub TASK 2 – A

                        USER2, time spent 6

                        USER3, time spent 4

Parent Task 3

      SUB TASK 1 – A

                        USER5,time spent 1

                        USER3,time spent 5

      Sub TASK 2 – A

                        USER2, time spent 6

                        USER3, time spent 4

Parent Task 4

      SUB TASK 1 – A

                       USER5,time spent 1

                        USER3,time spent 5

      Sub TASK 2 – B

                        USER1, time spent 6

                        USER3, time spent 4

[rubenmarin · Accepted solution]

Hi HAJ, maybe with:

Sum({<[Type]={A}, DisplayName=E(DisplayName), key=P({<[Type]={A}>} key)>} TimeSpent)

[hhajjali]

Great, thanks a lot Rubin