4 Replies Latest reply on Aug 27, 2018 11:41 AM by Trisha Ann

    Formula for multi row data

    Trisha Ann

      HI. I have some weird data and am stumped on a formula to transform the data into what I need now. Below is an example of my data. The NUMBER is repeated on each line there is a TASK NAME for that NUMBER and I have a check to reflect if the TASK NAME was done. 1 = done. I need to create a formula to show for that one NUMBER (example 103) has completed the TASK NAME if it has a 1 in the CHECK, but not count all of the rows the TASK NAME is repeating the CHECK. In example 103: CLEAN, COLLABORATE & VERIFY are repeated multiple times and it's not a set pattern on how many times they are repeated. If the number has a 1 for  the TASKNAME 'VERIFY' I'd like to only Count the CHECK 1 time.

      Ideally I need to see that NUMBER 103 has the sum of 1 for each TASKNAME Clean = 1, Collaborate = 1, Approve = 1 & Verify = 1.

      Any help is appreciated. I am torn whether I want to do this formula in Alteryx or Tableau, but would prefect Tableau. 

       

      DATA:

       

      NUMBER            TASK   NAME               CHECK    

      100                   CLEAN                         1

      100                   CLEAN                         1

      100                   CLEAN                         1

      100                   COLLABORATE             1

      100                   APPROVE                    1

      100                   VERIFY                         0

      101                    CLEAN                         0

      102                   CLEAN                         0

      103                   CLEAN                         1

      103                   COLLABORATE            1

      103                   COLLABORATE            1

      103                   APPROVE                    1

      103                   VERIFY                         1

      103                    VERIFY                        1

      103                    VERIFY                        1

      104                   CLEAN                         0

      105                   CLEAN                         1

      105                   COLLABORATE            1

      105                   COLLABORATE            1

      105                   APPROVE                    1

      105                    APPROVE                    1

      105                    VERIFY                         1

      106                    CLEAN                         0