topic Help with avg in subtotal pivot table in QlikView

Help with avg in subtotal pivot table

Wed, 21 Mar 2012 13:53:11 GMT

Hi all!

I need help with the presentation of a subtotal in a pivot table:

I calculate the average of a series of percentages (obtained from an expression whith set analysis )

The pivot table gives me back a total of 40% (maximum value of the column) when I need is an average ( 27%)

here is the pivot table results:

dimension 1           20%
Dimension 2           20%
dimension 3           20%
dimension 4           30%
dimension 5           30%
dimension 6           40%
TOTAL                    40%

when i need
Average 27%

In the forum, I've seen expresions with dimensionality () = 1 or = 2. but I could not get the average

I copy the expression I'm working with:

if (Dimensionality () = 1,

0,
(sum ({$ <AñoFactura = {$ (= max (AñoFactura))}>} Executed)
/ sum ({$ <AñoObjetivo = {$ (= max (AñoFactura))}>} Target))

)

Any suggestions?

excuse my english!!!!

Thanks!

Help with avg in subtotal pivot table

Wed, 21 Mar 2012 14:54:40 GMT

What is the dimensionality of your sub total?

It is returning 40 because it is doing the total executed/ total target.

You my be able to use aggr.

if (Dimensionality () = 1,

Avg(aggr(

(sum ({$ <AñoFactura = {$ (= max (AñoFactura))}>} Executed)
/ sum ({$ <AñoObjetivo = {$ (= max (AñoFactura))}>} Target))

, Dimension))

)

This (I believe, not sure without testing) should return the correct results for each dimension and then do the average for the sub total

Help with avg in subtotal pivot table

Wed, 21 Mar 2012 15:51:35 GMT

Thanksfor answering. I send you the real expression to be more clear:

Theexpression I’m using is the following (whit out dimensionality()) :

if((VendidoT1/sum({$ < TrimestreObjetivo={'T1'}, AñoObjetivo={$(=max(AñoFactura))}>}Objetivo))>1,1,

(VendidoT1/sum({$ < TrimestreObjetivo={'T1'}, AñoObjetivo={$(=max(AñoFactura))}>}Objetivo)))

ItWorks just fine but returns the following :

Vendedor	Cumplimiento T1
Vendedor1	100%
Vendedor2	0
Vendedor3	54%
Vendedor4	0
Vendedor5	23%
Vendedor6	64%
Vendedor7	51%
Vendedor8	24%
Vendedor9	42%
Vendedor10	2%
Total	60%

When I need 45% as Total AVG

I’vetried your recommendation as follow:

Avg(aggr(

if((VendidoT1/sum({$ < TrimestreObjetivo={'T1'}, AñoObjetivo={$(=max(AñoFactura))}>}Objetivo))>1,1,

(VendidoT1/sum({$ < TrimestreObjetivo={'T1'}, AñoObjetivo={$(=max(AñoFactura))}>}Objetivo)))

,Vendedor))

Butthis is the result:

Vendedor	Cumplimiento T1
Vendedor 1	0
Vendedor 2	0
Vendedor 3	0
Vendedor 4	0
Vendedor 5	0
Vendedor 6	0
Vendedor 7	0
Vendedor 8	0
Vendedor 9	0
Vendedor 10	0
Total	0

Anyidea what can be happening ?

Idon’t know if using aggr() function with set analysis in it is what generatethe error. It seems that the expression does not resolve the if() part first

Thankyou very much for your interest.

Help with avg in subtotal pivot table

Wed, 21 Mar 2012 15:58:07 GMT

Can you try it without the if statement to see if you return results? Is VendidoT1 a variable? if it is try using it as $(VendidoT1)

Avg(aggr(

($(VendidoT1)/sum({$ < TrimestreObjetivo={'T1'}, AñoObjetivo={$(=max(AñoFactura))}>}Objetivo))

,Vendedor))

Help with avg in subtotal pivot table

Wed, 21 Mar 2012 17:06:22 GMT

Excellent!

VendidoT1 was the title of a column of the pivot table.

I replace it for the hold expression and worked just fine!!

capo di tutti capi !!!

Thank you very much