topic Re: My average in Pivot Table is not correct in QlikView

My average in Pivot Table is not correct

mp802377 — Wed, 18 Jan 2017 00:42:58 GMT

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?

Re: My average in Pivot Table is not correct

mp802377 — Wed, 18 Jan 2017 01:26:08 GMT

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

Re: My average in Pivot Table is not correct

johnw — Wed, 18 Jan 2017 01:31:13 GMT

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?

Re: My average in Pivot Table is not correct

mp802377 — Wed, 18 Jan 2017 14:22:47 GMT

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

Re: My average in Pivot Table is not correct

johnw — Wed, 18 Jan 2017 17:05:55 GMT

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.

Re: My average in Pivot Table is not correct

mp802377 — Wed, 18 Jan 2017 23:22:42 GMT

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.

Re: My average in Pivot Table is not correct

johnw — Wed, 18 Jan 2017 23:50:34 GMT

Glad you figured it out.