# Qlik Sense Deployment & Management

Contributor III

## Average of Sum

Hello,

I have this pivot table and i would like to display the average of the sum of all Months :

this is the expression used :

if (Dimensionality()=1, Avg(Aggr( Count({<[Year of date]={"\$(=max([Year of date]))"}>}Num_Dossier)

/

Count({<[Year of date]={"\$(=max([Year of date]))">}distinct (Plaque)), Plaque,[Month of date] ,[Year of date])),

Count({<[Year of date]={"\$(=max([Year of date]))"},>}Num_Dossier)

/

Count({<[Year of date]={"\$(=max([Year of date]))"}>}distinct (Plaque)))

He display the value of janv and not the avg

Regards

1 Solution

Accepted Solutions
Contributor III

## Re: Average of Sum

I think i find the solution in the aggregation i need to put the year and the materiaux

7 Replies
MVP

## Re: Average of Sum

You may have encountered a granularity mismatch, won't you need to add Materiaux field to the Aggr() dimensions?

if (Dimensionality()=1, Avg(Aggr( Count({<[Year of date]={"\$(=max([Year of date]))"}>}Num_Dossier)

/

Count({<[Year of date]={"\$(=max([Year of date]))">}distinct (Plaque)), Materiaux, [Month of date] ,[Year of date])),

Contributor III

## Re: Average of Sum

Hello Stefan in the aggregation i added Materiaux and i removed plaque but the same issue,(it display the values of the first month

MVP

## Re: Average of Sum

You need to use the field that is used to display Materiaux dimension (so not the dimension label if it differs from the field name).

Could you post a small sample?

Contributor III

## Re: Average of Sum

Hello Stefan,

example:

for the first materiaux named FURGON ,in 2017 i need to have the average of (2.96 +3.21 +3.76 +3.24 + 1.20)/5=2.87

Regards

MVP

## Re: Average of Sum

Ok, but actually I was asking for a small sample application file or sample set of your data records.

Contributor III

## Re: Average of Sum

do you have please gmail id ?

Contributor III

## Re: Average of Sum

I think i find the solution in the aggregation i need to put the year and the materiaux