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.