Pivot tables - sum of a calculated column

[#1]

Hello,

I have a problem with my pivot table. I have defined some dimensions, which are not relevant to this calculation.

The first two columns (cost and amount) are calculated as sum(XYZ) resp. count(amount). The third column (Cost per Amount) is calculated as follows: sum(cost)/count(amount).

Now I would like to have the total sum of the third column. If I choose "show partial sum" the pivot table shows my the sum of columns 1 and 2, which is fine, but the third column is still the ratio of cost to amount.

Is there a way I can also calculate the third column as sum?

I'd really appreciate your help! Thanks!

[Anonymous]

Hi Stephanie,

Please check out chapter 72, Nested Aggregations and Related Issues, in the reference manual for a more thorough explanation.

Basically you'll want to do something along the lines of:

sum(aggr(sum(XYZ)/count(Amount), Dim1, Dim2))

Dim1 and Dim2 would be your dimensions.

[#3]

Hi,

Can you upload your application

[#4 · Accepted solution]

Hi Stephanie,

If you use a Straight table, you can go Properties chart, sheet expressions and chosse "Sum of Rows" option. See the image below.

But, if you use a Pivot Table, you should use the Aggr() function. The expression will be:

Sum(Aggr(sum(Cost) / Count(Amount), Dimension1, Dimension2, ..., DimensionN))

Where Dimension, is fields that has the pivot table as dimensions.

Attached, there's a qvw example. I hope will be helpful.

Regards,

Ricardo

[#5]

Thank you all!

The Aggr() function worked perfectly fine for my problem.

Regards,

Stephanie

[#6]

Thank you all!

The Aggr() function worked perfectly fine for my problem.

Regards,

Stephanie