3 Replies Latest reply on Oct 9, 2018 12:31 PM by Hari Ankem

    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

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

      3) Sum 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.

              1 of 1 people found this helpful