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.
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.
SUM(IF DATETRUNC('week',[Date]) = DATETRUNC('week',[WeekSelect]) THEN [Error Cnt] ELSE 0 END) / COUNTD([Date])