topic Average in a horizontal Pivottable in QlikView

Average in a horizontal Pivottable

Fri, 27 Aug 2010 10:59:14 GMT

Hi out there,

i got a problem, where I can't find a solution in the forum. Maybe someone can give me a hint.
I have 2 dimensons Script and Hour.

Example:


 13 14 15
Skript A
Skript B
Skript C

The formula gets me an average value for each Skript and Hour. Now I want to color each value which is higher than the average value of the complete row. How can I build a formula which only applies on the specific row?

Thanks for your help.

Average in a horizontal Pivottable

Fri, 27 Aug 2010 11:35:14 GMT

Try using Aggr() function


aggr(avg(Val),Skript)

use the above expression to check the cell condition

Average in a horizontal Pivottable

jonathandienst — Fri, 27 Aug 2010 11:38:11 GMT

If I understand you correctly, just create a text colour or background colour formula for the expression.No need to build one for each row.

The expression colour formula could be something like:

=If(Column(1) > Avg(Total <Script> Column(1)), Red())

Column(1) refers to the value in the first expression. You could also use the expression itself. Change as necessary.

Jonathan

Average in a horizontal Pivottable

Fri, 27 Aug 2010 12:59:05 GMT

Hi,

sry, i didnt worked out. No field gets marked with a red background color. It seems that avg(total <Skript>Values) doesnt work correctly.

The idea is that an row looks like this:


 1 2 3 4 5
Skript1 5 6 7 8 9
Skript2 1 2 3 4 5

The bold value should be red 😉 The value in the fields is calculated by a simple avg() function.

Average in a horizontal Pivottable

jonathandienst — Wed, 01 Sep 2010 13:32:14 GMT

Hard to say without more information. Perhaps you could post you model so that we can have a look in more detail.

Jonathan