Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You are using Upload date in Aging.
Please check, what is the last Uploaded Date.
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
Can you post sample app
Hi,
Try including Update Date as dimension in the chart?
Regards,
Jagan.
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 |