7 Replies Latest reply on Mar 10, 2016 3:55 PM by patrick.byrne.0

    Looking for Average of spreads calculation

    Erin Williams

      I have the entire school district's student roster by family code. This includes grade levels and schools.  We are looking to cut the data in a few ways

       

      How many families are in our community?  CNTD(Family Code)

      What is the average family size in our community? just made a table and totaled by average, if there's a better way to do this let me know

       

       

      The following questions are the most pressing:

       

      How can I bucket or bin the family codes by count of records (aka I want a dimension for family size)

      How many families have children in two schools, three schools?

      What is the average grade difference between siblings?

      If a family has 3 or more kids, what's the Grade(age) spread oldest to youngest?

      For those same 3+ families, for the district, what is the average or median age spread?

      What is the average # of schools a family has kids at?

       

      Any help would be greatly appreciated

        • 1. Re: Looking for Average of spreads calculation
          Shinichiro Murakami

          Erin,

           

          It's quite tough to explain everything.

          Could you play around the workbook.

           

           

          How can I bucket or bin the family codes by count of records (aka I want a dimension for family size)

          How many families have children in two schools, three schools?

          What is the average grade difference between siblings?

           

          If a family has 3 or more kids, what's the Grade(age) spread oldest to youngest?

          For those same 3+ families, for the district, what is the average or median age spread?

           

           

          What is the average # of schools a family has kids at?

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Looking for Average of spreads calculation
            patrick.byrne.0

            Erin,

             

            Let me preface this if you need any additional information, clarification or help, feel free to reach out.

             

            There are several different things asked for in this post, I did my best to address all the issues in my sample workbook and post.

             

            + For the first requirement [How many families are in the community?]

              + For this I created a calculated field.

                  1. Right Click in the Data Pane>Create Calculated Field

                  2. To acquire the number of Families utilize the following function:

                  COUNTD([Family Code])

                  * In the sample workbook I have attached this Calculated Field is name [CountD of Famlies]

             

             

            + For the Average size of Family.

              + I used another calculated field to find the average family size.

                  1. Right Click in the Data Pane>Create Calculated Field

                  2. Next to get the average family size the following function will be used:

                  { FIXED [Family Code]: COUNT([Family Code])}

                  *Calculated Field is named [Size of Family]

                  3. The above calculation is applying the fixed level of detail expression. This is telling the run the count calculation down the table of data counting every single point that has data in the [Family Code] field.

                  4. Adding this to a view

                  5. Change aggregation of measure from sum to average

                        a. Right Click [Family Size]>Measure> Average

             

             

            + To get the average of grade difference between Siblings I had to use multiple calculations

                  + First, I need to account for the 'Grade Level' that are stored as letters, for example PK and K

                  + Using the below calculation we are able to assign a 0 and -1 respecitvely for the two grades.

                        Pk and KT Grade Converter

                        IF ([Grade Level] = 'PK') THEN -1 ELSEIF ([Grade Level] = 'KT') THEN 0 ELSE INT([Grade Level]) END

                  + Next we want to utilize this function to create another calculated field to accomplish the desired result of average grade difference between siblings

                  + Creating another calculated with the below calculation

                        {FIXED [Family Code]: MAX([Pk and KT Grade Converter]) - MIN([Pk and KT Grade Converter])}

                  + This utilizes the calculated field created for the PK and KT conversion then reading down the data set, it is used to take the MIN and MAX of each family and return the difference.

                          This calculation is named [Difference Young to Old] in the sample workbook.

                  + Performing different aggregations we are able to use this to answer several questions that are posed in your post.

                        1. We can see the difference in age broken down by house, school, or overall for all families.

                        2. This can all be accomplished by utilizing the calculation you can see the difference in age broken down by the family level on 'Sheet 8'

                        3. On 'Sheet 9' you can see how the same function is used to calculate the average difference of the whole population.

             

            + To get the average difference in age for a family size of 3+

                  + Creating a new calculated field and using our field we created [Size of Family] and [Difference Young to Old] then use it in the following calculated field.

                        IF [Size of Family] >= 3 then [Difference Young to Old] END

                  + The above calculation will return only data points that satisfy the [Family Size] is greater than 3

             

            + On 'Sheet 9' there is a demonstration of the use a calculated fields the [Difference Young to Old] calculated field to show the average age difference across the schools.

             

             

            + Additionally there is a need to show the average schools per family. Utilizing the COUNTD function used earlier we are able to count the number of schools per Family. Utilizing the below function will accomplish the desired result of average schools per family.

             

              {FIXED [Family Code] :COUNTD([School Short Name]) }

             

             

            I believe the above information should help answer the questions raised in your post. Additionally I will attach an edited version of the workbook posted in the forum. I am also going to attach some articles and product help that I used to accomplish the desired results.

             

             

            + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +

             

             

            Functions

             

             

            http://onlinehelp.tableau.com/current/pro/online/windows/en-us/functions.html

             

             

            Fixed LOD Calculation

             

             

            http://onlinehelp.tableau.com/current/pro/online/windows/en-us/help.html#calculations_calculatedfields_lod_fixed.html?Highlight=fixed level of detail

             

             

            Calculated Fields

             

             

            http://onlinehelp.tableau.com/current/pro/online/windows/en-us/help.html#calculations_calculatedfields.html%3FTocPath%3DAdvanced%2520Analysis%7CCalculations%7CCalculated%2520Fields%7C_____0

            1 of 1 people found this helpful
            • 3. Re: Looking for Average of spreads calculation
              Erin Williams

              Thank you so much for your time in helping me figure this out.  It is quite amazing!

              • 4. Re: Looking for Average of spreads calculation
                Erin Williams

                Thank you so much for your time in helping me figure this out.  It is truly appreciated!

                • 5. Re: Looking for Average of spreads calculation
                  patrick.byrne.0

                  Of course! I am happy to help! If you liked my response please mark it as helpful! Hope you are able to understand your data a little better now!

                  • 6. Re: Looking for Average of spreads calculation
                    Erin Williams

                    Patrick, was wondering if you could help me with one last calculation

                     

                    We are looking to find out how many high schoolers have siblings in younger schools.  We are looking at changing our start times for high school and making them start latest, which would eliminate their ability to watch siblings.  We want to know how many families this could impact. 

                    • 7. Re: Looking for Average of spreads calculation
                      patrick.byrne.0

                      Erin,

                       

                      I was able to build this last calculation for you, please see the below explanation.

                       

                      Create a new sheet.

                       

                      First, we need to distinguish between the two groups of students identified in the question(High School and the Lower School). Those in high school and those that are in lower schools. I did this with a calculated field that placed a 1 for the lower schools and a 0 for all high school. *These are merely place holders for another calculation.

                       

                      This is the calculation I used.

                      Calculation Name: Highschool or Lower

                       

                      IIF ([Grade Level] >= '09' , 0 , 1 )

                       

                      Then convert this from a measure to a dimension

                       

                      Next I created a calculation that counted, Fixed on [Family Code] and Counting distinct on our above calculation. As this will give us a 1 or 2, a 2 indicating that there are both lower and high schools in the family.

                       

                      Calculation Name: Mix of Schools

                       

                      { FIXED [Family Code]: COUNTD([Highschool or Lower]) }

                      Convert the above calculation to a dimension.

                       

                       

                      Creating a new sheet and adding the [Mix of Schools] calculation as a filter and filtering out the number (1). This will give you only families with both a high school and lower school students.

                      1. Add [School Name] to Rows shelf

                      2. Add [Mix of Schools] to filters and select only the (2)

                      3. Add the [CountD of Families] to text on 'Marks' card.

                       

                      Let me know if you would like clarification on any of the above instructions. I have attached the workbook I created my solution in.

                       

                      Cheers,

                      Patrick