Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think i find the solution in the aggregation i need to put the year and the materiaux
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])),
Hello Stefan in the aggregation i added Materiaux and i removed plaque but the same issue,(it display the values of the first month
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?
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
Ok, but actually I was asking for a small sample application file or sample set of your data records.
do you have please gmail id ?
I think i find the solution in the aggregation i need to put the year and the materiaux