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

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
Specialist
Specialist
Author

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

View solution in original post

7 Replies
swuehl
MVP
MVP

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
Specialist
Specialist
Author

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

swuehl
MVP
MVP

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
Specialist
Specialist
Author

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

swuehl
MVP
MVP

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

zied_ahmed1
Specialist
Specialist
Author

do you have please gmail id ?

zied_ahmed1
Specialist
Specialist
Author

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