Hi, this is a bit hard to explain so please bear with me.
I have a sheet with several information regarding customers and house sales. Each customer makes a monthly payment towards the payment of the house. I was asked to produce a weekly report that shows how much is the total of all the customers debt at that particular week.
In this sheet I have a column called 'current debt'. Every time a payment is done in column 'payment', the current debt is automatically updated in another column.
There is another column that contains the date of payment from where I can take the week number by using the weeknum formula.
Ex
Client Name________Payment_____Payment date___Current debt___Week
Client 1____________ 50.000______01-01-2008______150.000______1
Client 1____________ 50.000______01-02-2008______100.000______5
Client 1____________ 50.000______01-03-2008______ 50.000______9
Client 1____________ 10.000______01-04-2008_______40.000______13
Client 1____________ 10.000______01-05-2008_______30.000______17
This created 2 problems as I want to creatr a pivot tabe to display all weeks of the year, consecutivelly,even if there are no payments in a specific week.
I already solved the problem of not having any payments in a specific week of the year by adding a dummy client that contains dummy data for all weeks of 2008.
My problem is the following:
Lets imagine that I create the Pivot table with just the information above and the dummy client up to week17 (all zero in payments and current debt for the dummy client)
I put the field 'Client name' in the 'row section' of the pivot, I have the field 'week' in the 'row section' of the pivot and have the field 'current debt' in the data section of the pivot with 'min' in the field option.
Here is my problem.
For week 1 I will get current debt for all customers as being 150.000, which is correct but in week 2, my current debt will be 0. Zero from the dummy customer and because there is oly a payment in week 5, zero from client1, same problem happens in week 3 and 4.
Any way I can get the pivot, possible formulas, macro I can use to fix this problem?
I basically want the pivot to display
week1 150.000
week2 150.000
week3 150.000
week4 150.000
week5 100.000
week6 100.000
and so on
Thanks in advance
Bookmarks