topic Re: Re: troubles in getting averages per dimensions in pivot chart in QlikView

troubles in getting averages per dimensions in pivot chart

Thu, 19 Dec 2013 13:55:35 GMT

Dear all,

I have a datatable which looks somewhat like the following

OrderLine	Category	Month
1111	on time	Jan
1112	on time	Jan
1113	delay	Jan
1114	delay	Feb
1115	delay	Feb
1116	delay	Mar
1117	delay	Mar
1118	on time	Mar
1119	on time	Mar

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

	Jan	Feb	Mar	Average
delay	33%	100%	50%	weighted average(33%;100%;50%)
on time	66%	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

Thu, 19 Dec 2013 14:03:39 GMT

Hi Erik

Are you doing avg(OrderLine) ?

Chris

Re: troubles in getting averages per dimensions in pivot chart

Thu, 19 Dec 2013 14:04:55 GMT

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

Gysbert_Wassenaar — Thu, 19 Dec 2013 14:28:27 GMT

See attached qvw

Re: troubles in getting averages per dimensions in pivot chart

Thu, 19 Dec 2013 15:04:34 GMT

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.

Hope I made myself clear and thanks for your input!

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

Thu, 19 Dec 2013 15:08:54 GMT

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

Gysbert_Wassenaar — Thu, 19 Dec 2013 15:22:23 GMT

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

Thu, 19 Dec 2013 15:52:09 GMT

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