Perform GROUP BY in Tableau

Question

enter image description hereI am working with NYC 311 data. I want to calculate the efficiency of each agency as follows:

Efficiency = No of days (Closed Date - Created Date)/ number of requests handled by the agency.

closed date = the date issue was closed created date = date when the request was received Each record is a service request handled by a particular agency. Each record contains created date, closed date, agency name, unique key and many other attributes.

The challenge here is to compute the number of requests handled by the agency. Each record is identified by a unique key. I have already created a visualization(bar graph) which shows the number of records in each agency. My idea is to display the efficiency as a line chart color-coded according to the agency. I have already generated a new measure in tableau which is called Processing time (using Date diff function) which gives me the number of days betweee\n closed date and created date for every record. I want to group the service requests according to agency, add the processing time column values for a particular group and then divide the aggregated processing time (for every group) by the number of requests handled by this group. Finally, I want to visualize this efficiency(avg no of days taken by each agency to complete a request) as a line chart or something different. I have just started learning tableau.

Any assistance is greatly appreciated Thanks!

enter image description here

Answer 1

Assuming that you have a dataset like this:

If you want to have a dual chart (bar for number of records and line for the average processing time) you have just to use use the dual axis option clicking on the second measure.

I think that Avg od Date Diff field is mor than fine for your purpouse. Am I right?

In your case, if I understand what you want, you should just replicate this scenario:

Which, by the way, is the same of getting this table: