topic Re: average but not avg() in QlikView

average but not avg()

Mon, 23 Sep 2013 11:18:17 GMT

I know avg is not that function I need, but I am not sure what is...

I have put all the details in my example file.

Thanks

Oli

swuehl — Mon, 23 Sep 2013 11:40:50 GMT

Try

=sum([# Order line value]) / count( distinct [Inv Month])

kji — Mon, 23 Sep 2013 11:54:32 GMT

sum([# Order line value])/Count (DISTINCT [Inv Month])

Mon, 23 Sep 2013 12:01:25 GMT

use this expression

sum([# Order line value])/Count(TOTAL DISTINCT [Inv Month])

find the attached Qvw

its worked fine.

adamwilson — Mon, 23 Sep 2013 12:04:43 GMT

this gives the desired result, although I would try to remodel the data to avoid using DISTINCT

sum([# Order line value]) / Count(DISTINCT [Inv Month])

Mon, 23 Sep 2013 12:08:38 GMT

hi i don't understand what u want clearly,do u want something like this?please check attached file

Mon, 23 Sep 2013 12:08:42 GMT

Like attached?

Or put this in your expression field:

=avg(aggr(sum([# Order line value]),[Inv Month]))

Mon, 23 Sep 2013 12:36:06 GMT

I am attaching your application with two options, one based on counting the number of months and the other using Dimensionallity().

MayilVahanan — Mon, 23 Sep 2013 12:43:27 GMT

Try like this

=If(RowNo() = 0 or IsNull(RowNo()), Sum([# Order line value]) / Count( DISTINCT

[Inv Month]), Sum([# Order line value]) / Count( [Inv Month]))

marcus_malinow — Mon, 23 Sep 2013 13:10:26 GMT

Hi Oli,

this expression should give you a cumulative sum of your [# Order Line Value] field. It should be simple to then calculate your average.

rangesum(above(total sum([# Order line value]), 0, only([Inv Month])))

Marcus

--edited as I missed out the rangesum!