topic Pick(Match()) inside Avg function in QlikView

Pick(Match()) inside Avg function

Tue, 23 Aug 2016 15:04:30 GMT

Hi there,

I am using Pick(Match(Field, '1','2','3','4'.'5'), exp1,exp2,exp3,exo4,exp5) in a line chart to show the productivity for 5 different sectors. Each expression inside the function uses a complex expression with variables. Now, I need to draw the average line inside the chart. When I use Avg() function with the above expression to calculate the average I get the message saying that "Nested aggregation not allowed". How can I get round this? My expression is

Avg(Pick(Match(Field, '1','2','3','4'.'5'), exp1,exp2,exp3,exo4,exp5) )

Many thanks,

Re: Pick(Match()) inside Avg function

jpenuliar — Tue, 23 Aug 2016 16:31:05 GMT

maybe this way:

Pick(Match(Field, '1','2','3','4'.'5'), Avg(exp1),Avg(exp2),Avg(exp3),Avg(exp4),Avg(exp5) )

Re: Pick(Match()) inside Avg function

Anonymous — Tue, 23 Aug 2016 16:55:39 GMT

Are your expressions containing the aggregate functions?

If yes then only "Nested aggregation not allowed" error comes.

Solution:

You have to use aggr along with the avg function

Avg(aggr(

Pick(Match(Field, '1','2','3','4'.'5'), exp1,exp2,exp3,exo4,exp5) ,DIMENSION FIELDS))

Then only it will work.

Post a sample app to show the functionality.

Regards

Nitin