12 Replies Latest reply on Sep 9, 2013 8:02 AM by Will Wyatt

# Weighted sum by distinct identifer

Hi all. I'm trying to calculate a weighted sum but I need to do it for a distinct row.

For instance, a simple set of my data will look like:

1/1/2010ArtsSciencesBiologyBIOL293112345Full-Time50
1/1/2010ArtsSciencesMathMATH101198765Full-Time30
1/1/2010ArtsSciencesBiologyBIOL188112345Full-Time50
1/1/2010ArtsSciencesBiologyBIOL101212345Full-Time30
1/1/2011ArtsSciencesBiologyBIOL293256789Part-Time28
1/1/2011ArtsSciencesArtBIOL293210101Part-Time28
1/1/2011ArtsSciencesBiologyBIOL293210101Part-Time28
1/1/2011ArtsSciencesBiologyBIOL293223587Part-Time28
1/1/2011ArtsSciencesBiologyBIOL293211111Part-Time28
1/1/2011ArtsSciencesBiologyBIOL293222222Part-Time28

I can succsessfully count of People by AcademicYear and sum Credits Sold by year to get something like:

2010

2011
Credits Sold160168
Distinct Count of PersonID25

However, I need to create a weighted sum of Positions, called FTE. The simple forumula is that a Part-Time person counts as 1/3 of a Full-Time person. So, I need my data to look like:

20102011
FTE

2

0

01.66666

Whenever I try to create a calculated for for FTE Tableau is counting each row in the source data because the data holds section and CreditsSold data which is needed for other sheets/calcuations. I need Tableau to distinct the PeopleIDs and their Position, then do the calculation. How is this possible?

Thanks.

• ###### 1. Re: Weighted sum by distinct identifer

I do not understand what the resulting table you've displayed for FTE is supposed to be displaying.  Can you explain this in a different way?  I'm certain whatever you are trying to do can be done, but I'm not sure I understand your problem.

• ###### 2. Re: Weighted sum by distinct identifer

Sure. I need to count the number of each position types by AcademicYear. I don't need to count them by rows in the dataset (since a PersonID may show up more than once. That is, they may teach more than once course per academic year).

So, in 2010, there were 2 distinct full time employees (who taught 4 classes, but I'm not concerned with that for this calculation). In 2011, there were 5 distinct Part-Time employees (who taught 6 classes). Part-time employees count at 1/3 Full-Time. So the FTE for 2011 would be 1.6666 ( 5 distinct employees * .33333).

I'm pretty sure I could create a new data source and do the distinct in the database, but that would be two different data sources for this workbook, which I'm trying to avoid.

• ###### 3. Re: Weighted sum by distinct identifer

Ok.  I understand the 1.666...  What are the other cells in the FTE table supposed to represent?

• ###### 4. Re: Weighted sum by distinct identifer

Oh. Sorry, I just realized I didn't format that correctly. The zeros are irrelevant. I really am just trying to get a total for the year. 2010 would be 2. 2011 would be 1.66666. If there were the 'other' position types for a year (which I should have included in the sample data) there would still only be one total for the year.

• ###### 5. Re: Re: Weighted sum by distinct identifer

Nevermind.  See attached.  You had created the criteria for your calculation, but weren't actually calculating anything.  In the attached, I created a measure for your CountD of PersonID, then another calc that multiplies your FTE by this, resulting in the 2.0 and 1.6667 shown.  One note in the FTEResult calc--I had to wrap the second part of the calculation in the ATTR function, since the first part of the calculation was an aggregate.  You cannot mix aggregates and non-aggregates in a calculation, so this is how I dealt with that.

Let us know if this isn't the result you're looking for.

• ###### 6. Re: Weighted sum by distinct identifer

If this resolved your issue, please remember to mark the question as answered in order to help keep the forum "clean"

Best of luck!

• ###### 7. Re: Re: Re: Weighted sum by distinct identifer

Hmm. I think the calculations stops working if a PersonID appears in more than one AcademicYear (which I did not show in the original dataset).

 Academic Year College Department Subject CourseID Section PersonID Position CreditsSold 1/1/2010 ArtsSciences Biology BIOL 293 1 12345 Full-Time 50 1/1/2010 ArtsSciences Biology BIOL 188 1 12345 Full-Time 50 1/1/2010 ArtsSciences Biology BIOL 101 2 12345 Full-Time 30 1/1/2010 ArtsSciences Biology BIOL 188 1 23587 Part-Time 50 1/1/2010 ArtsSciences Biology BIOL 101 2 23587 Part-Time 30 1/1/2010 ArtsSciences Math MATH 101 1 98765 Full-Time 30 1/1/2011 ArtsSciences Art BIOL 293 2 10101 Part-Time 28 1/1/2011 ArtsSciences Biology BIOL 293 2 10101 Part-Time 28 1/1/2011 ArtsSciences Biology BIOL 293 2 11111 Part-Time 28 1/1/2011 ArtsSciences Biology BIOL 293 1 12345 Full-Time 50 1/1/2011 ArtsSciences Biology BIOL 293 2 22222 Part-Time 28 1/1/2011 Bible Bible BIOL 293 2 23587 Part-Time 28 1/1/2011 ArtsSciences Biology BIOL 293 2 56789 Part-Time 28

If I drop [CountD PersonD] on the sheet, the counts are correct. For this example data, FTE result if blank.

• ###### 8. Re: Re: Re: Weighted sum by distinct identifer

It shouldn't stop working.  I don't know what you mean, you'll have to exemplify this in your sample workbook.  The screenshots aren't really helping me understand what the problem is, or what solution you are expecting.

• ###### 9. Re: Re: Re: Re: Weighted sum by distinct identifer

I updated the extract so that a PersonID can appear in more than one AcademicYear. I added MeasureNames to demonstrate the empty cells. I really do appreciate your taking the time to help with this.

• ###### 10. Re: Re: Re: Re: Re: Weighted sum by distinct identifer

Not sure if this is what you're looking for, but I'll attach anyway as I don't have any more time to work on this today.

The fact is, I've only been using Tableau a few months--so maybe someone else will chime in and provide a better solution.  There are usually many ways to accomplish tasks in Tableau.

• ###### 11. Re: Weighted sum by distinct identifer

I have some similar problem too. I would like to add the load or contact hours of each PersonID so that I can determine if they are part time or full time students and then determin how many FT/PT in each division. How can I aggregate them or what function should i use?

• ###### 12. Re: Re: Weighted sum by distinct identifer

Tableau and I went back and forth. They helped me get much further, but I'm still having issues if I need to add an aggregated weighted measure with another that isn't weighted. Anyhow, this is what I ended up doing.

Create a new calculation called 'Index()'. The formula is just 'Index()'.

Create a new Table calculation that is the weighted calculation you're trying to figure out. The formula for the table calc, for me (calculating based on a field called Position), is something like

```WINDOW_SUM(IF ATTR([Position])="Full-Time" THEN COUNTD([PersonID]) ELSE COUNTD([PersonID])*(1/3) END)
```

This table calculation needs to be computed using the field that drives the calculation. In my case, Position.

You must add the Index calculation you created as a filter. Only let the filter return 1. This selects the first item to count against in each 'bucket' that is being counted (I think, I'm a little fuzzy about how this works for the whole viz).

Finally, add the driving field (for me, Position) to the level of Detail.

Hope this helps.