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

    Weighted sum by distinct identifer

    Will Wyatt

      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:

      Academic YearCollegeDepartmentSubjectCourseIDSectionPersonIDPositionCreditsSold
      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
          Matt Lutton

          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
            Will Wyatt

            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
              Matt Lutton


              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
                Will Wyatt

                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
                  Matt Lutton

                  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
                    Matt Lutton

                    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
                      Will Wyatt

                      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 YearCollegeDepartmentSubjectCourseIDSectionPersonIDPositionCreditsSold
                      1/1/2010ArtsSciencesBiologyBIOL293112345Full-Time50
                      1/1/2010ArtsSciencesBiologyBIOL188112345Full-Time50
                      1/1/2010ArtsSciencesBiologyBIOL101212345Full-Time30
                      1/1/2010ArtsSciencesBiologyBIOL188123587Part-Time50
                      1/1/2010ArtsSciencesBiologyBIOL101223587Part-Time30
                      1/1/2010ArtsSciencesMathMATH101198765Full-Time30
                      1/1/2011ArtsSciencesArtBIOL293210101Part-Time28
                      1/1/2011ArtsSciencesBiologyBIOL293210101Part-Time28
                      1/1/2011ArtsSciencesBiologyBIOL293211111Part-Time28
                      1/1/2011ArtsSciencesBiologyBIOL293112345Full-Time50
                      1/1/2011ArtsSciencesBiologyBIOL293222222Part-Time28
                      1/1/2011BibleBibleBIOL293223587Part-Time28
                      1/1/2011ArtsSciencesBiologyBIOL293256789Part-Time28

                      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
                        Matt Lutton

                        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
                          Will Wyatt

                          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
                            Matt Lutton

                            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
                              Sherry Sin

                              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
                                Will Wyatt

                                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.