Summing across a pivot table

[#1]

Hi All

Is it possible to sum across a pivot table to get an average over 2 months to perform a calculation?

Scenario

Attached pivot table screenshot

the calculation is

sum(stock) for Oct / ((sum  sales for Oct) +sum(Sales for Sep) / (count #weeks for Oct) + count(#weeks for Sep))

the Amount in Week for October should be 21 but currently I can only perform the calculation over 1 month which gives me 26

Cheers

Brett

[jagan]

Hi,

Try Chart Inter Record functions like Below()/Above()/Before()/After() like this

sum(stock) for Oct / ((sum  sales for Oct) +sum(Sales for Sep) / (count #weeks for Oct) + count(#weeks for Sep))

=sum(stock)/(RangeSum(sum(stock), Above(sum(stock))) / (RangeSum(Count(Weeks), Above(Count(Weeks)))

If above expression not works then try changing Before() or attach sample file.

Regards,

Jagan.

[sushil353]

Hi Brett,

Upload sample application for better resolution.

Sushil

[#4]

Hi Sushil

See attached document

[jagan · Accepted solution]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[#6]

Thanks Jagan, works well