topic Re: Calculating Average on Count(x) Expression in QlikView

Calculating Average on Count(x) Expression

Mon, 10 Aug 2015 13:13:22 GMT

Hello QV-Community

I try to calculate an average on an Count(x) expression. For a better understanding see the chart below.

The blue bars show the weekly count of radiology examinations. Now I want to calculate the average of this weekly count and show it as a horizontal line. I know, that there is a standard function for this, but I do not want to use this because I can not define the color properly. Se the following threat why:

Calculating average in weekly reporting of count of examinations

Due to the answer in this threat I want to realize the average line by a calculated reference line.

But I have now idea how to design an expression that fits my requirements. I tried things like Avg(Count(ID)). But I get not the right results. Is it even possible to use an standard expression on another standard expression???

Thank you for your help.

Achim

Re: Calculating Average on Count(x) Expression

sinanozdemir — Mon, 10 Aug 2015 14:24:42 GMT

Hi Achim,

Maybe something like this:

Avg(Aggr(Count(ID), Other Dimension)

You can also check this link by HIC

Average – Which average?

Re: Calculating Average on Count(x) Expression

Mon, 10 Aug 2015 15:09:53 GMT

Hi Sinan

Thanks for helping me again.

But I still do not come around with this expression. I tried:

But I already get an error in the editor field.

So I have no idea to calculate this average value. Thought about giving every record another field with a count of 1 to use "sum" instead of "count". But if I replace Count by Sum in the expression above it still gives me an error back.

Any further ideas?

Thought that should be easier.

Achim

Re: Calculating Average on Count(x) Expression

sinanozdemir — Mon, 10 Aug 2015 15:13:12 GMT

You didn't close the Avg bracket:

Avg(Aggr(Count(ID), Week))

Re: Calculating Average on Count(x) Expression

Mon, 10 Aug 2015 15:40:27 GMT

... too tired to see! Think I am dealing with this problem for too long. Sorry! You are totally right.

I am much closer now. Added "year" as a further dimension and get my desired average line

Thanks a lot.

Achim

Re: Calculating Average on Count(x) Expression

sinanozdemir — Mon, 10 Aug 2015 15:43:40 GMT

It happens to everyone.

I am glad to help.

Take care.