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

# Count Calculations w/ Multiple Criteria

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

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

• ###### 2. Re: Count Calculations w/ Multiple Criteria

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

• ###### 3. Re: Count Calculations w/ Multiple Criteria

You can create multiple calculated fields and then have 2 worksheets, one for the counts and the other for percentages. 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

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

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