I am creating a balance sheet using a pivot table. In the table the formula for balance is this:
if(fiscalperiod=0,0,creditamt + debitamt) +openbalance))
This works great. However I need to add another formula that is only calculated for the total of the pivot table for thew L&E (Not needed per dimension)
if(left(text(segvalue1),1)<>'1',if(fiscalperiod=0,0,creditamt + debitamt) +openbalance)
At the moment I have this formula in a seperate list box, b ut if they want to export the information to Excel, this info would not be included.
Is there any way to add it to the pivot table, but have it only appear at the bottom as a total?
I think you could try this:
In presentation tab, set show partial sums only for the top dimension, and deselect for all other dimensions.
Then use as expression something like:
= if(rowno()=0,TOTALEXPRESSION, EXPRESSION)
where TOTALEXPRESSION is the one you want to use at the total level, and expression the one per dimension.
(ensure that both work as expected at the given location, for example take a look at the help regarding sum of rows with pivot tables).
show partial sums is grayed out on my chart.
I got it to work by adding a dimension 'Total', then told the expression that it can not equal any of the values of the next dimension, which suppresses it for all levels except the total.
That's sound interesting. But I don't get what you mean with telling the expression that it can not equal....
Could you post your expressions for the total dimension/expression or a sample app here?
I added Dimension 1 as ='Total'
Dimension 2= Topincome ( values only of General and Administrative,Sales, Cost of Goods Sold, and Selling)
=if(Topincome<>'General and Administrative'
and Topincome<>'Cost of Goods Sold'
Since the only place where topincome='' or is null is on the total level of the dimension total, this is the only place the value shows up.
This is really only usable since I only have 4 values on the next dimension. If it were more, then it would not make since to use this solution.
Ok, getting closer.
But then you have a dimension 'Total' / additional column in your chart, don't you?
And you are using a partial sum to show the total line, right?
Thanks for sharing,
P.S: just saw your image, forget question 1)...
Got it (and just learned about the indent mode)!
I think indent mode is also why the partial sums are grayed out.
So in the future, if the number of your dimension values increase or you want to get rid of the extra column,
I think you might wanna try again the above expression using rowno().
(This works also with indent mode, if you set partial sums with indent mode deselected, then switch to partial sums).
I've attache a sample of what I had in mind.
Anyway, thanks for sharing your solution, have a nice evening / day,
I just read, understood and implemented what u hv shared above.
This is extraordinary feature.
Thanks a lot for sharing.
Keep it up.