Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Sum in Pivot Table using Set Analysis

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?

1 Solution

Accepted Solutions
Not applicable

Sum in Pivot Table using Set Analysis

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

3 Replies
mov
Esteemed Contributor III

Sum in Pivot Table using Set Analysis

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

Not applicable

Sum in Pivot Table using Set Analysis

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?

Not applicable

Sum in Pivot Table using Set Analysis

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

Community Browser