4 Replies Latest reply: Mar 21, 2012 1:06 PM by marcelo gonzalez

# Help with avg in subtotal pivot table

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           2
0%
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

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,

0,

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

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

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

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

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