topic Totaling a column in a Pivot Table in QlikView

Totaling a column in a Pivot Table

Mon, 07 Jun 2010 21:33:12 GMT

I'm having a problem with summing a column in a pivot table. I use the following formula for the Actual Revenue column which works correctly for each line item:

If(StatusID='Firm',
sum((numJTDEstimatedSal) * 1.12) ,
sum(PostAmt * RevFlag)
)

When you look at the subtotals or totals, only the status IDs <> 'Firm' are summed up. Sample data is listed below. Can anyone please tell me how I would go about getting the whole 'Actual Revenue' column to sum up correctly?

Thank you!

	Status	Estimated Revenue	Actual Revenue
	Billable	$57,235.27	$57,233.27
	Billable	$30,504.26	$29,105.26
	Billable	$10,524.00	$10,524.00
	Billable	$60,101.40	$0.00
	Billable	$1,396.00	$0.00
	Firm	$51,272.00	$57,424.64
	Firm	$24,000.00	$26,880.00
	Firm	$39,000.00	$43,680.00
	Total	$274,032.93	$96,862.53
Total		274,032.93	$96,862.53

Totaling a column in a Pivot Table

johnw — Tue, 08 Jun 2010 01:04:35 GMT

Pivot tables always evaluate the expression for the total instead of summing the rows. On the total line, the StatusID has TWO values, not one, so evaluates to null in your comparison, giving you the else value. So your total line is probably sum(PostAmt * RevFlag)?

To do a sum of rows in a pivot table, you use aggr(). The basic form is this:

sum(aggr(your expression, your dimensions))

So in your case, something like this if I'm understanding your pivot table:

sum(aggr(If(StatusID='Firm',
sum((numJTDEstimatedSal) * 1.12) ,
sum(PostAmt * RevFlag)
),StatusID))

I think you can find more information under "advanced aggregation" in the help text.

Totaling a column in a Pivot Table

Tue, 08 Jun 2010 14:26:40 GMT

John,

Thanks so much for your response and for the explanation! The aggr() function is exactly what I needed and everything is working now.