My average in Pivot Table is not correct

[mp802377]

I have this example table below.

ID	Start Date/Time	Category	MTR	Duration
1	28-Jan-16 19:19	AR	02:13	2:13:00
2	25-Jan-16 09:12	AR	00:54	0:54:00
3	14-Jan-16 09:31	AR	00:40	0:40:00
4	13-Jan-16 23:06	AR	01:04	1:04:00
5	12-Jan-16 22:45	AR	01:17	1:17:00
6	12-Jan-16 10:01	AR	01:12	1:12:00
7	07-Jan-16 22:25	AR	01:06	1:06:00
8	03-Jan-16 03:00	AR	07:15	7:15:00

If you take this table in Excel and do an average of MTR, the average is 01:57. In Qlikview my dimensions are category and MonthYear. My expression is Avg(MTTR) and I also tried (Sum(MTTR))/(Count(MTTR)) and it comes to 01:53. That is a difference of 4 minutes. All of my averages are off 3-7 minutes.  I tried Aggr(Avg(MTTR),category) and came up with the same number. I don't like that though because when I remove 2016, it does an aggregation of all years and I want year over year or month over month depending on the chart. What am I doing wrong?

[mp802377]

The column name is actually MTTR, above I typed it in.

[johnw]

Assuming you want the average of the averages instead of just the overall average, you'd need to list all your dimensions in the aggr, not just one of them. So I believe you want this?

avg(aggr(avg([MTTR]),[Category],[MonthYear]))

Though if you have both year over year and month over month for this data, those two charts are likely to have different average of averages. Are you sure you want the average of averages and not just the average?

[mp802377]

That formula is still giving me the 1:53, which is not correct. it needs to be 1:57.

[johnw]

For the data you posted, both a simple average and my average of averages formula (since there's only a single average) create the same value in QlikView, 01:57. If you can post data that duplicates the problem, perhaps I can help further. But without being able to duplicate your problem, it's very hard to solve your problem.

[mp802377 · Accepted solution]

I found my problem. It is the little things behind the scenes that will always get you. In my load statement, I have a join and that was multiplying my distinct ID. So, of course, my average was not providing the correct answer. Fixed the join (just created it as a separate table instead of a join) and that fixed that. An entire day on a little thing behind the scenes.....

I really wasn't trying to take an average of an average. My first was avg(MTTR), but that didn't work, nor anything else. I was grasping at straws. Now avg(MTTR) works.

[johnw]

Glad you figured it out.