5 Replies Latest reply on Oct 8, 2012 6:44 AM by Jonathan Drummey

    Averages Within Years Across Datapoints

    leonor.keller

      Hi,

       

      I have a database with multiple datapoints per year. I would like to code a calculated field that gives an average across datapoints for each year for several different variables. I have been unable to correctly write the code. How do I amend for my variable 'admssn':

       

      if academicyear = 1987 then sum([admssn])/count([admssn])
      elseif academicyear = 1988 then sum([admssn])/count([admssn])
      elseif academicyear = 1989 then sum([admssn])/count([admssn])
      elseif academicyear = 1990 then sum([admssn])/count([admssn])
      elseif academicyear = 1991 then sum([admssn])/count([admssn])
      ...


      elseif academicyear = 2009 then sum([admssn])/count([admssn])
      elseif academicyear = 2010 then sum([admssn])/count([admssn])
      end

       

       

      such that it sums and counts only the datapoints within the year. It currently gives me an error message that I cannot mix aggregate and nonaggregate comparaisons or results using the if function.

       

      Thank you,

       

      Leonor

       

      Message was edited by: Leonor Keller

       

      Message was edited by: Leonor Keller

        • 1. Re: Averages Within Years Across Datapoints
          Jonathan Drummey

          Hi Leonor,

           

          If you have the academicyear in the view somewhere - as a discrete (blue pill) - , then SUM([admssn])/COUNT([admssn]) will be generated for each academicyear (and the combination of other discrete pills in the view), and that way you won't have to adjust your calculations as there are new years.

           

          For the calculation as is, you'd need to wrap academicyear in one of the aggregation functions such as ATTR(), MIN(), MAX(), etc. I prefer ATTR() because it returns a value if there is one and only one value in the data range and * (a special version of Null) otherwise. You'd still need academicyear in the view to make sure that ATTR() is returning only value, and in that case you're just as well off using my suggestion above.

           

          Jonathan

          • 2. Re: Averages Within Years Across Datapoints
            leonor.keller

            Actually, if you don't mind, Jonathan, I have another quick question.

             

            The calculation gives me a yearly average as long as there is only the discrete (blue pill) Academic Year variable in the view. However, if I include another blue pill, discrete variable, such as Institution Name, in the same sheet and try to use my newly created calculated variable (average yearly admissions), it actually breaks down across institutions and becomes the same as the sum of admissions per institution per year. My next calculation is to calculate (institution-specific average yearly admissions)/ (average yearly admissions). That does not seem possible in this case, is that right? Would I need to follow the second guidelines to do that?

             

            Thank you again!

            • 3. Re: Averages Within Years Across Datapoints
              Jonathan Drummey

              Hi Leonor, what you are looking for there is calculations at different levels of detail, where there's the average admissions per year+institution and the average admissions per year. In Tableau, this generally calls for table calculations. I set this up in the attached, with:

               

              Year = YEAR(Order Date)

              Institution = Category

              Yearly Admissions = Admission Standin - using SUM(Sales)/COUNT(Sales)

               

              Based on the Yearly admissions, the formula [Admission Standin]/TOTAL([Admission Standin]) with Compute Using set to Category (so it partitions on the Order Date) gets the desired results.

               

              Jonathan

              • 4. Re: Averages Within Years Across Datapoints
                leonor.keller

                Thank you again; your response was very helpful !

                 

                Best wishes.