5 Replies Latest reply on Nov 13, 2018 1:27 PM by Brandon Boren

    Count Calculations w/ Multiple Criteria

    Brandon Boren

      I am extremely new to Tableau. I have been using Excel to format my data set while applying complicated formulas to calculate my result. However, I am wondering if Tableau is a platform that would be more user friendly and less complicated to utilize. I have atttached a *.twbx as well as provided a small example data set that I would like to do the following with:

       

      1) Count the total # of [Service Type] "DA" in the data set for each [Staff Name]

      2) Sum (by Staff Name) the # of occurrences each [ClientName] had exactly one [Service Type] "DA" AND exactly one [Service Type] "Ther Note"

      3) Sum (by Staff Name) the # of occurrences each [ClientName] had exactly one [Service Type] "DA" AND > 1 [Service Type] "Ther Note"

       

      Data Set:

       

      Desired Result:

       

      Any assistance/guidance is greatly appreciated. Let me know if you need additional information. Thanks!

       

      - Brandon

        • 1. Re: Count Calculations w/ Multiple Criteria
          Deepak Rai

          Please check your data and your requirement for dates. They don't match.

          • 2. Re: Count Calculations w/ Multiple Criteria
            Brandon Boren

            I have updated the requirement date range to reflect that of the range in the sample data.

            • 3. Re: Count Calculations w/ Multiple Criteria
              Hari Ankem

              You can create multiple calculated fields and then have 2 worksheets, one for the counts and the other for percentages.

              1.png

               

              Here is what I have created:

              Total DAs Completed: IF [Service Type]="DA" THEN 1 ELSE 0 END

               

              DA ONLY:

              IF {FIXED [Client Name]:SUM(IF [Service Type]="DA" THEN 1 END)}=1

              AND ZN({FIXED [Client Name]:SUM(IF [Service Type]="THER NOTE" THEN 1 END)})=0 THEN

                  1

              END

               

              DA + 1 THER NOTE:

              IF {FIXED [Client Name]:SUM(IF [Service Type]="DA" THEN 1 END)}=1

              AND {FIXED [Client Name]:SUM(IF [Service Type]="THER NOTE" THEN 1 END)}=1 THEN

                  1

              END

               

              DA + >1 THER NOTE:

              IF {FIXED [Client Name]:SUM(IF [Service Type]="DA" THEN 1 END)}=1

              AND {FIXED [Client Name]:SUM(IF [Service Type]="THER NOTE" THEN 1 END)}>1 THEN

                  1

              END

               

              Total DAs Completed Percent: 1

              DA ONLY Percent: SUM([DA ONLY])/SUM([Total DAs Completed])

              DA + 1 THER NOTE Percent: SUM([DA + 1 THER NOTE])/SUM([Total DAs Completed])

              DA + >1 THER NOTE Percent: SUM([DA + >1 THER NOTE])/SUM([Total DAs Completed])

               

              Hope this helps. 2018.2 workbook is attached.

              2 of 2 people found this helpful
              • 4. Re: Count Calculations w/ Multiple Criteria
                Brandon Boren

                I am now running into the issue where [ClientName] are seeing more than one [Staff Name]. If [Service Type] ="DA" was administered by Staff A and [Service Type]="THER NOTE" was adminstered by Staff B both "DA" and "THER NOTE" are being counted for both individuals, which is inaccurate.

                 

                I need assistance adding [Staff Name] into the calculation so that [ServiceType] is not being counted for by more than one [Staff Name].

                 

                Any assistance is greatly appreciated!

                • 5. Re: Count Calculations w/ Multiple Criteria
                  Brandon Boren

                  I think I got it figured out. See below:

                   

                   

                  DA ONLY:

                  IF {FIXED [Client Name],[Staff Name]:SUM(IF [Service Type]="DA" THEN 1 END)}=1

                  AND ZN({FIXED [Client Name],[Staff Name]:SUM(IF [Service Type]="THER NOTE" THEN 1 END)})=0 THEN

                      1

                  END

                   

                  DA + 1 THER NOTE:

                  IF {FIXED [Client Name],[Staff Name]:SUM(IF [Service Type]="DA" THEN 1 END)}=1

                  AND {FIXED [Client Name],[Staff Name]:SUM(IF [Service Type]="THER NOTE" THEN 1 END)}=1 THEN

                      1

                  END

                   

                  DA + >1 THER NOTE:

                  IF {FIXED [Client Name],[Staff Name]:SUM(IF [Service Type]="DA" THEN 1 END)}=1

                  AND {FIXED [Client Name],[Staff Name]:SUM(IF [Service Type]="THER NOTE" THEN 1 END)}>1 THEN

                      1

                  END