topic Re: Average? in QlikView

Average?

Sun, 09 Aug 2015 23:04:50 GMT

Hi everyone,

Please help me with another formula for the average

I try this:

sum(value)/count(distinct monthyear)

But, It ignores zeros months.

Best regards,

Rodrigo

Re: Average?

settu_periasamy — Mon, 10 Aug 2015 02:04:51 GMT

Hi,

Did you try without distinct..

Sum(value)/count(monthyear)

Avg(value)

Re: Average?

Mon, 10 Aug 2015 02:54:24 GMT

Hi,

I try without distinct, but I need the monthly average and the result is different from average y with con Avg(value) neither.

I need a function to count the months that are in the table and consider the zeros

Thank you,

Re: Average?

sunny_talwar — Mon, 10 Aug 2015 04:52:48 GMT

Not sure I understand, 16.25 seems like correct average where your Sum(Value) = 130 and Count (monthyear) = 8 and 130/8 = 16.25. What are you expecting to see?

Re: Average?

Mon, 10 Aug 2015 22:50:45 GMT

Hi,

When I have the function count(monthyear) the result is not equal to 8 and when I have the function count (distinct monthYear) is not the result 8, so the do with that very long expression.

Best regards,

Rodrigo

Re: Average?

sunny_talwar — Tue, 11 Aug 2015 03:57:32 GMT

Try this may be:

Max(TOTAL Aggr(Count(DISTINCT monthyear), Item))

Re: Average?

jonathandienst — Tue, 11 Aug 2015 05:26:43 GMT

It will not count months that are missing (ie no entries containing that month). You will need to populate a master calendar with the missing months. This might help:

How to populate a sparsely populated field

Re: Average?

Tue, 11 Aug 2015 12:26:25 GMT

Thank you very much Sunnny

Re: Average?

sunny_talwar — Tue, 11 Aug 2015 12:43:52 GMT

Not a problem. I am glad it helped.

Best,

Sunny