topic Re: Average Function Ignoring Null and Not providing correct value. in QlikView

Average Function Ignoring Null and Not providing correct value.

Wed, 26 Aug 2015 15:42:44 GMT

Hi Friends,

i am new to qlikview.

I am using Average function from qlikview.

I have some row which has no data.

but, still , it's needs to count in average calculation.

but, somehow, qlikview is ignoring that.

i have also replaced that field with 0 when it's blank,

but, still, average shows wrong value.

can someone help,

what would be work around ,

so, Average function can count blank/null rows ?

thanks!.

Re: Average Function Ignoring Null and Not providing correct value.

marcus_sommer — Wed, 26 Aug 2015 15:47:00 GMT

Maybe you could get your result without avg() with sum(value) / count(value).

- Marcus

Re: Average Function Ignoring Null and Not providing correct value.

Wed, 26 Aug 2015 15:59:48 GMT

yes, that correct,

but,

we need to get this using AVG function instead of Sum and Count.

Re: Average Function Ignoring Null and Not providing correct value.

marcus_sommer — Wed, 26 Aug 2015 16:15:38 GMT

I don't think that this will work - NULL will be general ignored from expressions and 0 will be handled as FALSE and I believe that this will be also ignored by most of the expressions - but why is avg() a must?

Further see here for averages: Average – Which average?

- Marcus

Re: Average Function Ignoring Null and Not providing correct value.

jagan — Wed, 26 Aug 2015 16:17:22 GMT

Hi,

Try something like this

=Avg({<DimensionName*={'*'}>} MeasureName)

=Avg({<MeasureName*={'*'}>} MeasureName)

=Avg({<MeasureName=P(MeasureName)>} MeasureName)

HOpe this helps you.

Regards,

Jagan.

Re: Average Function Ignoring Null and Not providing correct value.

Wed, 26 Aug 2015 17:35:19 GMT

Thanks.

i need to use this measure in denominator.

i have tried -

=Avg({10000/<MeasureName*={'*'}>} MeasureName)

but its not working.

any help would be appreciated

Re: Average Function Ignoring Null and Not providing correct value.

rbecher — Wed, 26 Aug 2015 17:41:57 GMT

This is wrong set analysis syntax. Can you please upload an example to illustrate the problem.

Re: Average Function Ignoring Null and Not providing correct value.

jagan — Thu, 27 Aug 2015 02:05:37 GMT

HI,

If you want to divide 10000 with the expression the try like below

=10000/Avg({<MeasureName*={'*'}>} MeasureName)

If you provide sample data and your expected value then it would be easier to give solution.

Qlik Community Tip: Posting Successful Discussion Threads

Regards,

Jagan.