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.
ATR_131126_sk.twbx 46.6 KB