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?
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
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)
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".