Pivot Table Total Sum

[#1]

Hi friends,

I have stuck in a requirement see I have a table where I have opening balance and Closing balance with Sales field.Lets assume following table.

Month	Opening	Sales	Closing
Jan	150	20	160
Feb	160	50	200
Mar	200	10	220
Apr	220	80	310
May	310	20	400
Total	150	180	400

as in the above table I have to calculate Total where for opening total is its starting value i.e. 150 and for Closing its last value i.e. 400 and for sales it is sum(sales) .

Does anyone has any idea how to solve this.

Regards

Bhawna    

[Anonymous]

Like the attached ?

I used a Chart of type Straight Table

On the Expression tab for the Expression Total mode I used :

First String

Sum   

Last String

[gmoraleswit]

Here is another solution using the Dimensionality function

Hope this helps

Regards!

[#4]

I need to use pivot table but thanks for replying I can use this in some another issue.

[#5]

My requirement is kind of different I need to use some extra field and Cross the column to a row so for me its difficult to put your suggestion.I am sharing a snapshot of my table,hope you will get idea how to do this.

I used Partial sum option thats why Total for opening and closing is showing sum of all value but I need first value for opening and last value for closing.

[gmoraleswit]

You can use the Dimensionality() function regardless of the number of dimensions you have

Dimensionality() should give you 0 for your grand totals row

[#7]

But then it will show the opening stock value in each dimension column,means in your example You are considering only A but when you one more dimension suppose B then it will show the value for B but I need separate column for Opening and Closing.