6 Replies Latest reply: Nov 18, 2016 4:10 AM by HAJ ALI Haidar RSS

    Using sum and criteria

    HAJ ALI Haidar

      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:


      Image1.png


      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.

        • Re: Using sum and criteria
          Ruben Marin

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

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

            • Re: Using sum and criteria
              HAJ ALI Haidar

              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?

                • Re: Using sum and criteria
                  Ruben Marin

                  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)


                    • Re: Using sum and criteria
                      HAJ ALI Haidar

                      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