Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
Not applicable
Author

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))

View solution in original post

4 Replies
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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))

Not applicable
Author

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