Sum in Pivot Table using Set Analysis

[#1]

Hi Guys,

Yes, yet another question about set analysis and pivot tables - this time i want to show the sum of all values in a column.

This is my formula:

=If(GLLedger = 'N',Sum({$<Month =>} If(InYearToDate(GLTranDate, vMaxDate, 0, 7), GLTranAmount)))

This sums at partial sum levels but not as a grand total. I've tried to re-arrange the formula to nest the IF statement inside the Sum statement, but this does not work.

I look forward to reading Oleg's post about Flags which would presumably solve this, but is there a simpler and easier way?

[Anonymous]

Matt,
Try this, I'm using something close to it in my recent application:
=sum({$<GLLedger='N', Month =>} if(InYearToDate(GLTranDate, vMaxDate, 0, 7), GLTranAmount))

[#3]

Thanks for your reply Michael,

I've tested your formula and I lose the YTD values in the column with all rows defaulting to $0.00.

Any other ideas?

[#4 · Accepted solution]

It is all about the nesting of the formula.

Below is the solution:

=Sum({$<Month =>} If(InYearToDate(GLTranDate, vMaxDate, 0, 7), If(GLLedger = 'Y',GLTranAmount)))