Calculating average without zero

[Eva]

I have a special report where I do average as below:

Avg:
total(case when [date] in_range ?Date1? then measure else null end)/
total(case when [date] in_range ?Date1? then 1 else 0 end)

(see this post for more details: http://www.cognoise.com/index.php/topic,32777.0.html)

Now, I have to make calculation of average without zeros.
I can do it in an usual average expression using this statement:
case when [Measure]=0 then null else [Measure] end

But when I want to use it in the above calculated expression it does not work. I am getting errors running validation.

Any ideas how to solve this?

[hespora]

really the only thing that should change is your denominator; the numerator will stay the same if zeroes are added in.

So it should be

Code Select Expand


total(case when [date] in_range ?Date1? then [measure] else 0 end)
/
total(case when ([date] in_range ?Date1?) and ([measure]<>0) then 1 else 0 end)


[Eva]

Thank you hespora for looking into this!

Yes, it makes sense. I tried to use the suggested expression, unfortunately I still receive errors during validation, after I fill the prompts:

UDA-QOS-0006 Error
UDA-SQL-0107 A general exception has occured during the operation "prepare".

QE-DEF-0459 CCLException
RQP-DEF-0177 An error occured while performing operation 'sqlPrepareWithOptions' status='-9'
UDA-SQL-0107 A general exception has occured during the operation "prepare".