Pivot table totals and column references

[#1]

Hi All,

I have a simple requirement which i hope you can help me with. The application was developed by a client and i'm trying to find the simplest solution.

See the pivot table below:

In column 6, 'Est Cost to Complete', I want to show only the positive values from the formula Column(8) - Column(5).

The existing formula is:

if(Column(8) - Column(5) < 0,0,Column(8) - Column(5))

You can see the pivot totals do not show the total of the values in the column, rather the expression is also performed at the total level.

Does anyone have a solution?

FYI

Column 8 formula is : sum(EHHBudget)

Column 5 formula is :

Sum({$<FinYear=, Month=>} if(ProjectTranDate >= '01/01/2010',ProjectTranAmount)) + Sum({$<FinYear=, Month=>} if(left(ProjectCode,4) = '70EH',POLineCommitmentAmount))

[Miguel_Angel_Baeyens]

Hello Matt,

As a quick suggestion I'd use for colum 6

If([Restart Budget] - [Total Costs to Date] < 0, 0, [Restart Budget] - [Total Costs to Date])

to refer the row rather than using the whole column.

Hope that helps

[#3]

Miguel,

I tried the formula and found the same result as the screenshot attached in the original post.

[Miguel_Angel_Baeyens]

Matt,

I got you wrong, you will need to play with dimensionality() function in your expression to show the grand total with different sums. Something like

If(dimensionality() = 0, sum(something), If(Column(8) - Column(5) < 0, 0, Column(8) - Column(5))

Dimensionality() allows you to write depending on the level of expressions.

Hope that helps

[#5]

It looks as if i can achieve the same with the rowno() function in this instance as dimensionality(), however it looks like a powerful function.

I'm not sure how to use it to sum the ordinary lines and ignore the total line calculation.

[Miguel_Angel_Baeyens]

Hi Matt,

As I suggested in the sample code above, you will have to use a conditional in each expression where the rows differ from the totals, as I'm guessing you want to keep totals, but to calculate them in a different way.

Regards