aggr with average

[Anonymous]

Hi i am using aggr function to derive a sum but i need to slice the data into quarter  where the subtotal should be average of 3 month attched hereiwth  is the data for the same

Thanking You

Vinayagam

[swuehl]

Maybe like attached?

[Anonymous]

Thanks Sir

But if u c the total below the centre its is giving avg wherein i require the sum of average

Thanking You

Vinayagam

[swuehl · Accepted solution]

Ok, you want averages for horizontal total and sum for vertical total of AVGPO, right?

Then you need to use Dimensionality() and SecondaryDimensionality() to switch between different expressions on total cells.

See attached AVGPO2 expression.

[qlikviewwizard]

Hi swuehl

Good one.

[qlikviewwizard]

Hi vinayagam.121‌

swuehl ‌

Has given in PO TEST_2.qvw in AVGPO2.

Which value do you want to get?

[Anonymous]

Exactly sir  thank u

[Anonymous]

sir in the same line could we get the sum of the subtotal VERTICAL total of horizontal avg

[swuehl]

If I've understood correctly, just change the order of dimensionality checks:

=if( SecondaryDimensionality()=0,

avg( aggr( SUM(PO)/ COUNT(DISTINCT(Date)), Centre, MONTHNAME)),

if( Dimensionality()=0,

sum( aggr( SUM(PO)/ COUNT(DISTINCT(Date)), Centre, MONTHNAME)),

SUM(PO)/ COUNT(DISTINCT(Date))

))