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

# Formula for multi row data

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:

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

• ###### 1. Re: Formula for multi row data

Hi, Trisha

Please find my solution attached as well as below screenshot for your reference.

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Formula for multi row data

Hi Trisha,

You can try below logic:

{fixed [NUMBER ],[Task] : max(if  [CHECK]=1 then [CHECK] end)}

1 of 1 people found this helpful
• ###### 3. Re: Formula for multi row data

Thank you!

• ###### 4. Re: Formula for multi row data

Thank you!