Qlik Community

Qlik Sense Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

zied_ahmed1
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 :

error.PNG

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
zied_ahmed1
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
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])),

zied_ahmed1
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
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?

zied_ahmed1
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
MVP

Re: Average of Sum

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

zied_ahmed1
Contributor III

Re: Average of Sum

do you have please gmail id ?

zied_ahmed1
Contributor III

Re: Average of Sum

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

Community Browser