7 Replies Latest reply: Dec 19, 2013 10:52 AM by Erik Furlanis

# troubles in getting averages per dimensions in pivot chart

Dear all,

I have a datatable which looks somewhat like the following

OrderLineCategoryMonth
1111on timeJan
1112on timeJan
1113delayJan
1114delayFeb
1115delayFeb
1116delayMar
1117delayMar
1118on timeMar
1119on timeMar

The end result that I would like to obtain in a pivot table in QV is the following:

JanFebMarAverage
delay33%100%50%weighted average(33%;100%;50%)
on time66%0%50%weighted average(66%;0%;50%)

I think the result is pretty self explanatory.

I can get this with absolute values (i.e., counting of lines)

but as soon as I try the avg() function, either with or without Dimensionality(), I either get 100% everywhere or no values.

Can you please someone help me out,  it would be so much appreciated!!

thank you so much!

• ###### Re: troubles in getting averages per dimensions in pivot chart

Hi Erik

Are you doing avg(OrderLine) ?

Chris

• ###### Re: troubles in getting averages per dimensions in pivot chart

yeah, i also tried this one amongst other formulas.

Should that be the good one?

• ###### Re: troubles in getting averages per dimensions in pivot chart

See attached qvw

• ###### Re: troubles in getting averages per dimensions in pivot chart

Dear Gysbert!

it is working almost perfectly; only, the total averages part is giving wrong results.

With the formula you input, the total averages per month are averaged, regardless of the number of lines.

Meanwhile I would like to have the average for the whole amount of lines, regardless of the months.

• ###### Re: Re: troubles in getting averages per dimensions in pivot chart

I came up with this piece of script and it seems working as expected.

Could someone please confirm if it is valid?

```if(SecondaryDimensionality()=1,
count(OrderLine)/count(total <Month> OrderLine),
sum(aggr(count(OrderLine)/count(total OrderLine),Category))
)
```

Thanks!

• ###### Re: Re: troubles in getting averages per dimensions in pivot chart

If that's your definition of a weighted average then you might as well use simply count(OrderLine)/count(total <Month> OrderLine) as expression.

I thought you wanted the average of the month values: weighted average(33%;100%;50%). That's what my first expression calculates.

• ###### Re: Re: troubles in getting averages per dimensions in pivot chart

I admit that I have sometimes troubles in expressing what I really want/need and I perhaps take a more complicated than necessary route.

But I thought, in this case, that the "weighted average" term was the most appropriate

[...] where instead of each of the data points contributing equally to the final average, some data points contribute more than others.

so, if Feb has less line than Mar, it will count less.

But indeed, "average of the total" would have been an easier way of saying.

And indeed, the formula reported by you work as expected!

thank you