Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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
Miguel,
I tried the formula and found the same result as the screenshot attached in the original post.
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
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.
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