assistance required with pivot tables and totals

[diffeyj]

Hi All,

I have a pivot table where there are a couple of columns I want totalled and a couple I don't. It is a table based on stock and periods.

It shows for each stock item, by month, what the qty sold was, value sold, cost of sales, etc. As well as some fields like qty on hand and cost on hand. The issue is that I don't want the second group of expressions to be totalled, because that means they double up. I don't want to try anything tricky and work out what the total "should be", I just don't want to show it.

Any ideas?

thanx

josh

[Anonymous]

Hi Joshua,

I think you'll have a better shot at getting help on this if you can post an example document that illustrates your needs.

Right now it's kind of hard to guess what you display and what you're looking to sum up.

[diffeyj]

Hi Johannes,

I have attached a quick sample file. In there you will see that I want to show the value sold as well as the cost sold, as well as the qty on hand and cost on hand for each period.

I want to total the value sold and cost sold, this way I can see for a selection of periods what has been sold. However the qty on hand and cost on hand are the qty on hand not as at now, but as at the end of that period. So I can look back and see how the stock levels were back then (was there too much stock, not enough stock, etc).

So if I was to purchase 1000 of "M1" in January, then sell say 200 in the same month, then I would have 800 Qty on Hand at the end of Jan. If in Feb I don't purchase any more stock, but sell another 200 then it should be 600 Qty on Hand at the end of Feb. This is no probs. However it says that I have 1400 (800+600) on hand in my totals. I would prefer there were no totals for Qty on Hand cause it will just confuse the users.

Hope this clarifies.

[Anonymous · Accepted solution]

Hey Joshua,

A quick fix would be to change the expression of "Qty at hand" to:

=if(Len(Period)<>0,sum([Qty on Hand]), last([Qty on Hand]))

Let me break it down and explain what it does.

1. If the length of the value for Period is different from 0 (i.e. for every Period value) it calculates the Sum([Qty at Hand]), just like before.

2. However, when the length of the Period value is 0 (in the total field), it instead picks the last value of [Qty at Hand] which should give you the value that you want in the total, instead of the sum of the different period snapshots.

Hope this makes sense.

[diffeyj]

ideally I didn't want the total column at all, but for the time being I have it saying "N/A", as there were a few extra complications in the live file.

Thanx for the help