topic Sum in Pivot Table using Set Analysis in QlikView

Sum in Pivot Table using Set Analysis

Tue, 28 Jul 2009 07:49:43 GMT

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?

Sum in Pivot Table using Set Analysis

Anonymous — Tue, 28 Jul 2009 07:59:00 GMT

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))

Sum in Pivot Table using Set Analysis

Tue, 28 Jul 2009 08:14:13 GMT

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?

Sum in Pivot Table using Set Analysis

Tue, 28 Jul 2009 14:42:48 GMT

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)))