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
"Debra Dalgleish" wrote:
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
RD Wirr wrote:
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" wrote:
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 wrote:
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
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html