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

# Annualized averages (weighted aggregation)

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)

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)

Thanks, I will take a crack at it.

• ###### 3. Re: Annualized averages (weighted aggregation)

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

thanks

Jim

• ###### 4. Re: Annualized averages (weighted aggregation)

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)

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)

Selena -

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

Thanks,

Dan