PivotTable Calculations

[RD Wirr]

I have a large table of inventory transaction data that generally looks like
this
Data
Date Item Source Destination Qty
20-2-07 Item1 Bin1 Bin2 2
20-2-07 Item1 Bin1 Bin2 2
21-2-07 Item1 Bin2 Bin3 3
20-2-07 Item2 Bin1 Bin2 1
20-2-07 Item2 Bin1 Bin2 2
21-2-07 Item2 Bin2 Bin3 2

Each transaction includes the Items that are transferred from one Bin to
another and the Source and Destination Bins are named and the Qty of each
item transferred.
I subtract the Qty of items from the source Bin and Add the same items to
the Destination Bin in each transaction
I am trying to make a PivotTable that summarrizes these transactions and
calculates the net Qty of items in each Bin that would give these results
from the data above.
I also need to filter this data within Start and End dates.

PivotTable
Date
Sum of Qty Source
Item Bin1 Bin2 Bin3
Item1 -4 1 3
Item2 -3 1 2

Can anyone give me an idea how to approach this with the PivotTable
calculations?
Thanks in advance.
RDW

 

[William Horton]

I'm not sure if you can do this completely with a pivot table. However, if
you set your pivot table up so that Date is the page field, item and source
are the row fields, destination is the column field, and sum of Qty is the
data field then in another place in your worksheet you could make a table.
You would have to list all the items down a column (column A) and all the
bins across a row (row 40). Therefore, you would have to know all the
possible items and bins in advance. Then you could use GetPivotData
functions to get the answers you are looking for.

=IF(ISERROR(GETPIVOTDATA("Qty",$A$30,"Item",$A41,"Source",B$40)),0,GETPIVOTDATA("Qty",$A$30,"Item",$A41,"Source",B$40)*-1)+IF(ISERROR(GETPIVOTDATA("Qty",$A$30,"Item",$A41,"Destination",B$40)),0,GETPIVOTDATA("Qty",$A$30,"Item",$A41,"Destination",B$40))

Where $A$30 is just identifying the pivot table, $A41 and B$40 identifies
the item and bin which you can copy to other cells. When I did this I
obtained the exact results you listed in your post.

If you filter the pivot table using dates the formulas update accordingly.
However, the data may not be what you expect when you filter the dates.
You'll have to check it out.

I couldn't find any way to acomplish this with just a pivot table with the
way that the data table is set up.

Hope this helps.

Bill Horton

 

[William Horton]

I guess you could do this without even using the pivot table using sumproduct
functions. This issue is probably better done in Microsoft Access though.

 

[Herbert Seidenberg]

Assume your data looks like this:
Date Item Source Destination Qty
20-2-07 Item1 Bin1 Bin2 16
20-2-07 Item1 Bin1 Bin2 21
21-2-07 Item1 Bin2 Bin3 10
21-2-07 Item2 Bin1 Bin2 28
20-2-07 Item2 Bin1 Bin2 17
21-2-07 Item2 Bin2 Bin1 26
21-2-07 Item1 Bin3 Bin1 22
20-2-07 Item2 Bin3 Bin2 30
20-2-07 Item1 Bin1 Bin3 23
20-2-07 Item2 Bin3 Bin1 25

Create 2 Pivot Tables, for Gain and Loss.
Gain might look like this:

Item (All)
Gain Date
Destination Source 20-2-07 21-2-07 Grand Total
Bin1 Bin1
Bin2 26 26
Bin3 25 22 47
Bin1 Total 25 48 73
Bin2 Bin1 82 82
Bin2
Bin3 30 30
Bin2 Total 112 112
Bin3 Bin1 23 23
Bin2 10 10
Bin3
Bin3 Total 23 10 33

Layout:
ROW = Destination, Source
COLUMN = Date
DATA = Sum of Qty (relabel Gain)
PAGE = Item
Options:
Grand Totals for Rows
Click on Destination/Source. For both Field Settings:

Subtotals: Automatic
Show items with no data

Create the Loss Pivot Table:
Same as Gain, but with
ROW = Source, Destination

Create this:
NetGain
Bin1 -32
Bin2 76
Bin3 -44

To create the first formula (-32), click on (Grand Total)
of (Bin1 Total) of Gain (73) and subtract the corresponding
cell at Loss (105).
You can select the items and dates of interest from
the drop down lists.

 

[Debra Dalgleish]

What you want to do could be easily done in a pivot table, if you change
your source data to use two lines per transaction. For example:

TransID Date Item Bin Qty
1 20-2-07 Item1 Bin1 -2
1 20-2-07 Item1 Bin1 2
2 20-2-07 Item1 Bin1 -2
2 20-2-07 Item1 Bin1 2

If your data is similar to your sample, it should only take a bit of
cutting and pasting to create the duplicate set of records, and use
paste special to change the quantities for the source items to negatives

Then, create a pivot table with Bin in the Row area, Item in the Column
area, and Qty in the data area.

 

[RD Wirr]

Hi Bill,
Thanks for the input. This works well. The GetPivotData seems to take a bit
of time (I have a lot of data) but it is still 10 times faster than all the
descrete Dsum functions I have been using. As you mentioned this could be
done completely with Sumproduct's but because of the complexity of the actual
data (12 bins, 1000's of items and 10's of 1000's of data records) Sumproduct
is too slow and I have been using Dsum which more easily allows multiple
conditions and runs faster. I agree it would be better done in Access. I am
trying to work out the basic functionality in Excel first (which I am a
little better with) and then after we've proved it out for some months, port
it over to Access.

I am wondering if you can tell me a way to add a calculated condition to the
GetPivotData formulas? I still need to filter the data by a range of dates.
In other words I need to enter a Start date and End Date in a pair of cells
and then limit the data shown to the data records between those two dates. I
tried putting the Date field on the page field but is really cumbersome
having to go into Field Setting and select all the dates to hide. Each record
in my data has a date/time stamp and there are many 1000's.

Thanks again,
RDW

 

[RD Wirr]

Hi Herbert,

You have helped me in the past and I have been really impressed with your
solutions. In this case though, my actual data will cause problems with this
solution.

You are correct, on a smaller scale my data looks pretty much how you show
it but my actual data has close to a year of dates which will exceed the 256
column limit. Actually my dates are all date/time stamps that make is over
20,000 unique records. I have been playing with your idea of making a Gains
and losses PivotTables and then consolidating them but, I can't get over the
limitation of too many dates. In the end I still have to be able to filter
the resulting data to within a range of start and end dates. (not time stamps
but only whole days). Do you have an idea of another field I could use for
columns? I already tried swapping the date with the items but here again, I
have 1000's, too many.

I already have this functionality running with a sheet full of DSUM
operations (one row of Dsums for every item and one column of Dsums for every
Bin) but this is pretty cumbersome and really slow. The PivotTable seems to
be much faster and more easy to maintain than the Dsums. If you have another
idea, I am all eyes.

Thanks,
RDW

 

[RD Wirr]

Hi Debra,

Thanks for your help. I think this solution treats the data something like
an accounting General Ledger right? When I first set up this spreadsheet. I
considered doing it like that, (Well proven concept) but with the sheer qty
of records I was worried about running out of rows. So I put all the
conditional arguements in one row for each record. Now I can see some
disadvantages in my thriftiness but now to rework the rest of the system
around the concept will require a fair amount of tweaking. (there is lots of
data input logic that translates user input into database records). I am a
little hesitant for now to do this with a working system but it is due for an
overhaul soon so I will consider changing to your suggestion. I want to see
if it will make it run any faster so I will test it in the next day or two. A
good thing you noted in your solution to have a proper record ID. I have been
using the time/date stamps for uniqueness but there are problems with that
approach.

In the mean time, I wonder if you can point me in the right direction on how
to filter this data in a pivot table by start and ending date range. My data
all has date/time stamps (unique for each record) and just putting the date
field in the page field only shows one date. I need all records between two
dates. I am needing something that is easy for users to plug dates in a
couple of cells and have to PT change the data set.

BTW, I really like your contextures website. Lots of useful and accessible
tips. I refer to it all the time. Thanks for that.

Rgds,
RDW

 

[Debra Dalgleish]

Hi RD,
Thanks for letting me know that you like the web site.
To make it easy to filter for a date range, you could add a column to
the source data, e.g. ShowDate. Use a calculation to test if the date is
in the range between the start and end dates that the user has entered.
Add the ShowDate field to the pivot table's page area, and select TRUE
from the dropdown list.

After the user changes the dates, refresh the pivot table (manually or
programmatically, to show items in the new date range.
Debra

 

[Herbert Seidenberg]

Multiply the Qty by 1 or 0, depending on wether it
falls inside or outside the date range.
=Qty*IF(AND(Date>=Begin,Date<=End),1,0)
In Layout, remove the page entry and
move Item into COLUMN.
Update DATA to point to the new quantity.

 

[RD Wirr]

Hi Debra,
I tried your earlier suggestion of separating the in and out rows and having
all calculations in the PivotTable. That runs SO MUCH faster than any other
solution that I am going to implement this immediately. I tried your
suggestion on the date filter which also works well but slows the process
down noticably. I am hoping to find a solution along the lines of Herbert
Seidenberg's suggestion above if it can work within the PT. Otherwise this
will still work fine. Thanks again.
Regards,
RDW

 

[RD Wirr]

Hi Herbert,
Great idea of using 1 or 0 multiply to filter values. I am having trouble
with the implementation though. 1. Where do I put this formula? 2. If I move
the Item data field to Column Drop Area my 1000's of Items don't fit on the
sheet. Am I following you correctly here?

I tried to make a calculated field with this idea but PivotTables don't seem
to allow that. Anything I can do to implement this within the PivotTable
would be better. The pivotTable runs so much faster than discrete cell
functions...

Thanks again,
RDW

 

[Debra Dalgleish]

You're welcome, and thanks for all the updates you've provided.
It's nice to get the feedback, and it may help other people with similar
problems.