Hi Everyone,
I need some help with a pivot chart, let me start by explaing what i want to do.
I have a file with the following data, items, item category, value date and a total amount. What i want to do is create a sort of maturity profile. Where each item due in a certain period will be clubbed in a bucket as shown in the table below.
| Item Category | Total Amount | 1 week | > 1 week | 1 month | 1 - 3 months | 3 - 6 months | > 6 months |
|---|
| A | 500,000 | 1,000 | 15,000 | 2,000 | 85,000 | 7,000 | 390,000 |
| J | 64,799,456 | 456 | 709,000 | 50,000 | 4,040,000 | 20,000,000 | 40,000,000 |
| N | 563,398 | 5 | 310 | 3,083 | 20,000 | 10,000 | 530,000 |
So basically, item category will show the total amount (which is the amounts of all the items in that particular category) and which will be divided as in the table by the value date.
I have tried using this method,
Sum(If(AddMonths(Value_Date,3), Amt_F)) but this gives me the entire amount instead of the relevant part.
Can someone suggest a solution?
Many Thanks.
