PivotTable Calculations
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 |
PivotTable Calculations
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 "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 |
PivotTable Calculations
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. "William Horton" wrote: 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 "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 |
PivotTable Calculations
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. |
PivotTable Calculations
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 |
PivotTable Calculations
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 "William Horton" wrote: 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. "William Horton" wrote: 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 "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 |
PivotTable Calculations
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 "Herbert Seidenberg" wrote: 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. |
PivotTable Calculations
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 |
PivotTable Calculations
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 |
PivotTable Calculations
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. |
PivotTable Calculations
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 |
PivotTable Calculations
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 "Herbert Seidenberg" wrote: 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. |
PivotTable Calculations
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. RD Wirr wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
All times are GMT +1. The time now is 09:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com