Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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
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% |
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.
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))
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