I have set up a pivot table which aims to show the amount of expense used up
or accrued during the start Date and End date Specified by the user of the Pivot table
The data page has the fields Invoicer,Description,TotAmt,Gst,Net,StDate,EndDate,
Days(Which is EndDate-StDate +1) AmtPDay(which is Net / Days) & expense
This spreadsheet and its pivot table can be downloaded from
http://www.eggheadcafe.com/fileupload/-621222952_Copy of InvoicesToPayTest.zip
At present my Pivot table only adds transactions that have both the same start and end dates that are entered in the start date and end date filters. Therefore transactions that have expense accrued during the start and end date period but
1. Not starting on the start date
2. Not ending on the end date
3. occurring after the start date but before the end date
4. starting prior to the start date
5. ending after the end date
6. Starting before the start date and ending after the end date
All transactions of this nature are not included in the pivot table's reply to a particular start date and end date being set. For instance a transaction starting before the StDate and finishing after the EndDate continuing through the targeted date range which should be counted via days in date range * Amt per day - then returning this figure on the Pivot table. is a loop required ie Check each transaction ie row in data page to see if part or all of the transaction is within the date range specified by the user ie EndDate - StDate + 1
If so multiply No of days in range via AmtPDay and display expense label and its total over the specified period ie EndDate - StDate + 1. Is grouping by expense category needed so that only one figure for the expense category is generated. If this can be done i will be able to specify a date range and get the pivot table to display the amount of expense via expense category used up or accrued within the specified date range which would be an awesome tool to have if it works properly. Whilst the total unfiltered figure or total is correct sadly attempt to filter by 2 date rages ie StDate for the starting date and EndDate for the EndDate has been unsuccessful so far.
For instance September 2008 only 2 expenses Superannuation $700 and Telephone $138.13 resulting in total expenses being $838.13 being shown as expenses accruing between September 1 and September 30 inclusive (ie 30 Days ie End date + 1 less Start Date)
However in the file I have attached CopyOfInvoicesToPayTest The data
worksheet InvOutstanding contains 8 other expense items that are included in the month of September but have not been included in the pivot table report when the start date of September 1 and the end date of September 30 are entered on the pivot table sheet - Sheet 5 They are
Tot Amt
StartDt EndDate Days PerDay Sep 1 to 30
11/9 17/9 7 16.57 116
18/9 24/9 7 17.65 123.55
25/9 1/10 7 16.16 96.99
26/9 2/10 7 2.86 17.14
1/8 30/9 61 0.84 25.89
1/7 30/6/09 365 0.68 20.47
7/9 6/11 60 1.85 42.48
1/8 28/2/09 212 2.89 86.63
Instead of the total expense listed for September being 1366.45
only the two expenses with a start date of 1/9 and end date of 30/9
were listed which was $838.13.
To solve this problem the pivot table needs to add expenses that
meet the 6 criteria listed above. Does this need code or can
something be done to the properties of the pivot table filters so
that it will examine each start and end date and
1. Work out how many days of this expense fall within the date range
specified by user which is EndDate - StDate + 1
2. Multiply no of days in the date range by expense per day
3. If 1 expense category has muliple entry then sum these although
i think the Pivot table already does this.
I have attached the Pivot table file which has the worksheet invOutstanding
as the data worksheet and sheet5 as the Pivot table worksheet
at
http://www.eggheadcafe.com/fileupload/-621222952_Copy of InvoicesToPayTest.zip
Regards
Terry