1 Reply Latest reply on Nov 26, 2018 12:33 PM by Joe Oppelt

# Weekly and Monthly Averages from Daily Data?

I am allowing my users to select a week start date from a parameter and I'd like to show the week average of certain error codes. Summing them up gives a correct number but an average does not. Here's an example.

Let's say error code "A" has 10 errors on Sunday, 5 on Monday, 11 on Tuesday, 6 on Wednesday, 1 on Thursday, 0 on Friday, 9 on Saturday. Summing them would give 10+5+11+6+1+0+9 = 42. Averaging them SHOULD give me 42/7 = 5. However, I'm getting much smaller numbers like 1.5 and I'm assuming it's the way my table is structured since it's daily data

My calculated field is : AVG(IF DATETRUNC('week',[Date]) = DATETRUNC('week',[WeekSelect]) THEN [Error Cnt] ELSE 0 END)

It seems to be taking an average of each row of daily data instead of grouping them by day. How can I fix this issue? Same issue occurs with monthly averages.

• ###### 1. Re: Weekly and Monthly Averages from Daily Data?

A sample workbook would help here.  I can only guess without it.

If you want 42 divided by the number of DAYS on the sheet instead of the number of ROWS, you'll have to do that calc differently.

Something like:

SUM(IF DATETRUNC('week',[Date]) = DATETRUNC('week',[WeekSelect]) THEN [Error Cnt] ELSE 0 END) / COUNTD([Date])