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

Calculate average of minimum (while avoiding duplicate rows)

Hi,

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.

Thanks!!!

Stefanie

• 1. Re: Calculate average of minimum (while avoiding duplicate rows)

So heres a start with your minimum plots of time to service with an average line for all the minimum times...let me know what you think then we can go from there.

Cheers,

Mark

1 of 1 people found this helpful
• 2. Re: Calculate average of minimum (while avoiding duplicate rows)

Hi Mark,

Thanks a lot for taking a look at this so quickly.  I think I need to explain it a bit more.  It looks like what you've graphed is the minimum time to service for each month.  What I actually want is the average of the minimum time to service for all clients who started in a given month.  So for example:

* There are two clients who started in September, 2011

ClientID/ServiceDate/StartDate/_minServiceMinusStart

 27 10/3/2011 0:00 9/22/2011 0:00 11 27 10/9/2011 0:00 9/22/2011 0:00 17 27 10/12/2011 0:00 9/22/2011 0:00 20 27 10/19/2011 0:00 9/22/2011 0:00 27
 37 10/1/2011 0:00 9/2/2011 0:00 29

So the average that I am looking for is the average of 11 (time to 1st service for Client 27) and 29 (time to 1st service for Client 37), which is 20 for September 2011.

The worksheet actually shows 11, which is the minimum first time to service for all Clients who start in September, rather than the average of the minimum for each client.

Any idea how I can first group the _minServiceMinusStart by ClientID and then take the average of that (by month)?

Thanks for the help!

Stefanie

• 3. Re: Re: Calculate average of minimum (while avoiding duplicate rows)

Stefanie,

If we drop the Client ID on the marks card as a detail it will be included in the calc.  I'm still getting used to Tableau, lol.  Simplifed a bit and just used a reference line to average the minimum time to service.  Gives same 15.8 as the other calc, but a little easier.  What next?

Cheers,

Mark

1 of 1 people found this helpful
• 4. Re: Calculate average of minimum (while avoiding duplicate rows)

Hi Mark,

Thanks again for looking into this.  Adding the ClientID to the detail is a good tip.  The worksheet now shows the minimum for each client on the graph (for example, Sept. 2011 shows 11 and 29, which is correct according to the above example).  Now what I am trying to do is a secondary aggregation on top of that - I want a month by month average, not a total average.  So it would be a trend line with the following data points (Feb. 2011 = 10, Mar. 2011 = 10, Apr. 2011 = 13, June 2011 = 16.5, Aug 2011 = 13, Sept 2011 = 20 and so on). Any thoughts on how to do that?

I am wondering if I need to do this calculation outside of Tableau (e.g. my dataset will include the minimum time to service for each client), then I can use their average function.

Thanks!

Stefanie

• 5. Re: Re: Calculate average of minimum (while avoiding duplicate rows)

Hi Stefanie,

It is very helpful to know what the expected result is!

Without that I would not try to find a solution, because I am just a beginner when it comes to table calculations.

This is the formula used:

.

```WINDOW_AVG(MIN([TimeToService]))

```

.

I hope the attached helps you a step further.

Johan aka Kettan

.

• 6. Re: Re: Calculate average of minimum (while avoiding duplicate rows)

The average calculation can do that, just have to edit it so it uses the right partitioning.  Edit the calc, choose compute using advanced, then move client id over to addressing.  Partition is the "group by" and Addressing is the "changing across" attributes.

Looks like Kettan has them calculated as well.  ; )

Cheers,

Mark

1 of 1 people found this helpful
• 7. Re: Calculate average of minimum (while avoiding duplicate rows)

Hi Kettan and Mark,

Thanks to both of you (meant to mark both answers with the green star but it only lets you do one)!

This will take me a little time to digest - I am struggling to get the hang of calculations, partitioning etc, but this example will give me some good things to think about.

I am now trying to figure out how to get this into a line graph, but I'll think on that for a bit before asking for more of your time.

Thanks,

Stefanie

• 8. Re: Calculate average of minimum (while avoiding duplicate rows)

I am now trying to figure out how to get this into a line graph

If this includes hiding all but [month of start date] - to use as time dimension - and window_avg(min(timetoservice)) - to use as measure - then I expect you will meet a similar issue as I am seeking help for in  Count claims with reserve (exclude claims with no reserve). The issue was nicely formulated by Richard Leeke:

The reason that is hard for the left hand view is that you are wanting to filter on an aggregate at a different level of detail to the level shown in the view

I have a hope that the table calculation expert  Jonathan Drummey  will take a look at mine issue and therefore recommend you to follow my question so you are informed when comments are made.

• 10. Re: Calculate average of minimum (while avoiding duplicate rows)

Thanks Kettan, I'm reviewing that thread now!

Stefanie