Chart Avarage

[#1]

Dear All

I have created below pivot chart .

Open   Documents	Status	Count	Amount	Ageing
DOC1	Status1	182	3,218,498	27
DOC1	Status2	77	1,276,406	2
DOC1	Status2	18	210,981	8
DOC1	Total	277	4,705,885	19
DOC2	Status1	1	25,276	27
DOC2	Total	1	25,276	27
Total		278	4,731,161	19

where in Ageing is calculates as :

avg(Today()-[Upload date])

At the total row the average is not coming accurate. Hignlighted in bold.

In first case it should be 12.

Thanks in advance.

[ashwanin]

You are using Upload date in Aging.

Please check, what is the last Uploaded Date.

[jonathandienst]

Hi

12 is a simple average of the 3 numbers, whereas 19 is the correct weighted average of the underlying data. If for some reason, you need the simple average, then you need to do something like this:

=Sum(Aggr(Avg(<your age expression>),[Open Documents],Status))

(insert your expression for age; the aggr() needs all the dimensions in your chart)

HTH

Jonathan

[anbu1984]

Can you post sample app

[jagan]

Hi,

Try including Update Date as dimension in the chart?

Regards,

Jagan.

[#6]

HI All

Thanks for suggetions.

Had made small change in formula and used like this

if(Dimensionality()=0,avg(Today()-[Upload date]), avg(aggr(avg(Today()-[Upload date]),Status,Open Documents)))

which is giving perfect result.

Open   Documents	Status	Count	Amount	Ageing
DOC1	Status1	182	3,218,498	27
DOC1	Status2	77	1,276,406	2
DOC1	Status3	18	210,981	8
DOC1	Total	277	4,705,885	13
DOC2	Status1	1	25,276	27
DOC2	Total	1	25,276	27
Total		278	4,731,161	19