avg with only not null value

[#1]

hi,

first, sorry for my bad english ... , i'm french ...

in tab joined, i have a total avg calculation that is not like i would ...

total avg 0,14 = (0.11+0.28+0.17) / 4

but i would like to divide by 3 and not by 4 because there is only 3 months with value not null

thanks for help !

[sunny_talwar]

What is the expression you are using right now to perform that calculation?

[marcus_sommer]

You could count your period-dimension and then use an expression like:

sum(YourExpression) / count(YearMonth)

If your dimension YearMonth isn't a field from an associated dimension-table (Master Calendar) to the fact-table then the count() might be extended to:

sum(YourExpression) / count(distinct YearMonth)

     or

sum(YourExpression) / count({< YourValueField = {">0"}>} distinct YearMonth)

- Marcus

[#4]

expression is

  avg(aggr(sum([Nbr rmks],[Short AF Line group label],[AF Line group code],[Year month])/aggr(sum([nbr flt]),[Short AF Line group label],[AF Line group code],[Year month]))

[Nbr rmks] and [nbr flt] come from 2 different fact-table

[Short AF Line group label],[AF Line group code] from same table (not fact-table)

[Year month] from an other table (not fact-table)