topic Re: Average is not working. in QlikView

Average is not working.

Wed, 25 Nov 2020 16:16:04 GMT

I have the below expression..but it is not working...how can I make thi s work

avg(((Sum({$<[Lease Type]-={'Our Subtenant'}>}[Rentable Area (sq ft)])) +

(Sum({$<[Lease Type]={'Our Subtenant'}>}[Rentable Area (sq ft)])* -1)))

Thanks much.

Re: Average is not working.

sunny_talwar — Fri, 06 May 2016 18:31:11 GMT

Pavana the syntax isn't right. Avg(Sum(...)) will never work. You need to add Aggr() between them... with some dimensions over which you are doing the inner aggregation which is ultimately used for Avg(). But without more details, it would be difficult to say more. Can you may be provide a sample with expected output?

Avg(Aggr(RangeAvg(Sum({$<[Lease Type]-={'Our Subtenant'}>}[Rentable Area (sq ft)]), -Sum({$<[Lease Type] ={'Our Subtenant'}>}[Rentable Area (sq ft)])), SomeDimension/sHere))

Tried to simplify your expression a little bit. You just need to add Dimension or Dimensions on which you want to do the inner aggregation.

Re: Average is not working.

Fri, 06 May 2016 18:50:39 GMT

This is what my data looks like and I am converting Rentable area to negative value where Lease type is subtenant and then I need the avg for the Rentable area.

Re: Average is not working.

sunny_talwar — Fri, 06 May 2016 18:56:14 GMT

May be this:

Avg(Aggr(RangeAvg(Sum({$<[Lease Type]-={'Our Subtenant'}>}[Rentable Area (sq ft)]), -Sum({$<[Lease Type] ={'Our Subtenant'}>}[Rentable Area (sq ft)])), building))

Re: Average is not working.

swuehl — Fri, 06 May 2016 20:00:33 GMT

You could also create a flag in your script, like

LOAD

building,

[Rentable Area (sq ft)],

[Lease Type],

If([Lease Type] = 'Our Subtenant',-1,1) as factor

FROM ...;

Then your aggregation would boil down to

=Sum( [Rentable Area (sq ft)] *factor)

And for the average I would use

=Sum( [Rentable Area (sq ft)] *factor) / Count(DISTINCT building)