10 Replies Latest reply on Dec 3, 2013 12:56 PM by Stefanie Kenny

    Calculate average of minimum (while avoiding duplicate rows)

    Stefanie Kenny


      I am struggling with understanding joins vs. blends and aggregate calculations.  I am trying to build a dashboard with wide variety of different dimensions.  I've created a data extract with multiple joined tables from an Excel document.  Roughly the tables are as follows:

      Clients - ClientID (primary key), demographic info, StartDate

      Service - ClientID (multiples expected), ServiceDate, ServiceCategory

      Interviews - ClientID (multiples expected), InterviewType (e.g. intake or follow up), InterviewDate, Question1, Question2, Question3 ...


      I am trying to understand if I can use raw data and have Tableau perform most calculations, or if I need to instead provide 1 very simple stacked dataset (which seems like it should be unnecessary). 


      Here are two examples of what I'd like to do:

      1) Time to service by month of start - i.e. for all clients that started in a given month (StartDate), what was the average time until their first service?

      2) Service categories with a time slider - i.e. what is the count of services (row) delivered within a given ServiceCategory (column) by ServiceDate (page)?


      Taking #1 as an example, I know that I need to get the minimum of the difference between the StartDate and ServiceDate for each client, and then average that across all clients that started in a given month. I've tried various calculated fields and table calculations and at the end of the day, I really don't know what I'm doing and have not happened to luck out by accidentally finding the right combination. Please see the attached workbook: TimeService is the worksheet of interest - I've included everything else here for information. Each dot represents the minimum time to service (which represents the time to first service) for a single client, but I don't know how to average the dots!!


      Taking # 3 as an example (Services worksheet), I've tried using # rows but that doesn't work because all of my joins create duplicates.


      I feel as if I'm approaching the data preparation incorrectly, and that is the root of the problem, although I also struggle a lot with table calculations.  If anybody has any advice or suggestions, I would really appreciate it. I've tried reading all the articles on joins vs. blends, prepping your spreadsheet, using SQL etc, and I think there is something more fundamental that I'm missing.