6 Replies Latest reply on Oct 10, 2018 5:18 PM by Dan Cory

    Annualized averages (weighted aggregation)

    Selena Farris

      I am trying to create a table of annualized or mean-of-monthly averages. Basically, average all the januarys, februarys, etc. prior to taking an average so that having non integer years will not screw my results (wind speeds are generally higher in the winter, so having 15 months will skew results depending on the duplicate months). I tried the code:

       

      SUM(IF month([Endts]) = 1 THEN avg([Wind Speed])*31

      ELSEIF month([Endts]) = 2 THEN avg([Wind Speed])*28.25

      ELSEIF month([Endts]) = 3 THEN avg([Wind Speed])*31

      ELSEIF month([Endts]) = 4 THEN avg([Wind Speed])*30

      ELSEIF month([Endts]) = 5 THEN avg([Wind Speed])*31

      ELSEIF month([Endts]) = 6 THEN avg([Wind Speed])*30

      ELSEIF month([Endts]) = 7 THEN avg([Wind Speed])*31

      ELSEIF month([Endts]) = 8 THEN avg([Wind Speed])*31

      ELSEIF month([Endts]) = 9 THEN avg([Wind Speed])*30

      ELSEIF month([Endts]) = 10 THEN avg([Wind Speed])*31

      ELSEIF month([Endts]) = 11 THEN avg([Wind Speed])*30

      ELSEIF month([Endts]) = 12 THEN avg([Wind Speed])*31

      END) / 365

       

      But this produces the error. Is there a way I can do this with filters or another calculation? I am attaching the calculation required in excel if this isnt clear.

        • 1. Re: Annualized averages (weighted aggregation)
          Jim Dehner

          Hi

          I will give you a conceptual answer and you can take it from there

           

          I would create a simple file that had month and number of days in 2 columns (think lookup table in excel)

          then I would join that tabel (left join) with the table that has the avg wind speed by month - join on month -

          the join is like dropping an added column in the data with the number of days in the month

          the you get the weighted average by summing (probably with window_sum((avg(wind speeds)*avg(days)),-11,0)  / 365

           

           

           

           

          that may not be exact and yo may need to fight with the syntax

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Annualized averages (weighted aggregation)
            Selena Farris

            Thanks, I will take a crack at it.

            • 3. Re: Annualized averages (weighted aggregation)
              Jim Dehner

              Let me know how it works and if it helps please mark my response correct

              thanks

              Jim

              • 4. Re: Annualized averages (weighted aggregation)
                Dan Cory

                Your calculation is equivalent to Jim's suggestion of using a lookup table.

                 

                If you just want to fix your calculation, you just need to help Tableau know what to compute the average over:

                SUM(

                {INCLUDE MONTH([Endts]):

                IF month([Endts]) = 1 THEN avg([Wind Speed])*31

                ELSEIF month([Endts]) = 2 THEN avg([Wind Speed])*28.25

                ELSEIF month([Endts]) = 3 THEN avg([Wind Speed])*31

                ELSEIF month([Endts]) = 4 THEN avg([Wind Speed])*30

                ELSEIF month([Endts]) = 5 THEN avg([Wind Speed])*31

                ELSEIF month([Endts]) = 6 THEN avg([Wind Speed])*30

                ELSEIF month([Endts]) = 7 THEN avg([Wind Speed])*31

                ELSEIF month([Endts]) = 8 THEN avg([Wind Speed])*31

                ELSEIF month([Endts]) = 9 THEN avg([Wind Speed])*30

                ELSEIF month([Endts]) = 10 THEN avg([Wind Speed])*31

                ELSEIF month([Endts]) = 11 THEN avg([Wind Speed])*30

                ELSEIF month([Endts]) = 12 THEN avg([Wind Speed])*31

                END

                }

                ) / 365.25

                 

                And if you want to make it faster, use a CASE instead of IF. You can also factor out the avg.

                SUM(

                {INCLUDE MONTH([Endts]): avg([Wind Speed])*(

                CASE month([Endts])

                WHEN 1 THEN 31

                WHEN 2 THEN 28.25

                WHEN 3 THEN 31

                WHEN 4 THEN 30

                WHEN 5 THEN 31

                WHEN 6 THEN 30

                WHEN 7 THEN 31

                WHEN 8 THEN 31

                WHEN 9 THEN 30

                WHEN 10 THEN 31

                WHEN 11 THEN 30

                WHEN 12 THEN 31

                END)

                }

                ) / 365.25

                 

                Dan

                1 of 1 people found this helpful
                • 5. Re: Annualized averages (weighted aggregation)
                  Selena Farris

                  When I try the second, I get an error Cannot mix aggregate and non-aggregate arguments with this function. I get a similar error message on the first one as well. Any ideas?

                  • 6. Re: Annualized averages (weighted aggregation)
                    Dan Cory

                    Selena -

                     

                    Sorry that didn't work. Can you post a workbook with some sample data so I can see your entire problem?

                     

                    Thanks,

                    Dan