How can we calculate the Sum of Rows in a Pivot Table ?

[umartareen]

Hi All,

I have an expression that goes like this

if(Allow = 'Y', SUM({$<DirAmount={'-1'}, Contract = {'1'}>} POAmount + ConAmount),

  if(Allow <> 'Y' and Contract = '1',

  SUM({$< DirAmount={'-1'}, Enddate = {'>=$(vEndDate)'}>} POAmount + ConAmount), 0))

I am getting the correct values when displaying this in a straight table with checking the Sum of rows checkbox or when exporting to excel.

How can I display the sum of row values in a pivot table ? Or even display it in a straight table by checking the Expressions Total option.

Thanks,

Umar

[swuehl · Accepted solution]

You can do this using advanced aggregation (aggr() function). There is a chapter in the Help on how to use aggr() function to emulate sum-of-rows in a pivot table.

Basically it looks like:

= sum(

          aggr(

if(Allow = 'Y', SUM({$<DirAmount={'-1'}, Contract = {'1'}>} POAmount + ConAmount),

  if(Allow <> 'Y' and Contract = '1',

  SUM({$< DirAmount={'-1'}, Enddate = {'>=$(vEndDate)'}>} POAmount + ConAmount), 0))

           ,YourChartDim1, YourChartDim2)

)

You need to replace YourChartDim1 etc. with all your chart dimensions (fields only!).

[umartareen]

Great ! Thank you Sir.

Regards,

Umar