0 Replies Latest reply: Jul 4, 2011 6:54 AM by msadrikhcb RSS

    Spread total over different time periods

       

      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 CategoryTotal Amount1 week> 1 week1 month1 - 3 months3 - 6 months> 6 months
      A

      500,000

      1,00015,0002,00085,0007,000390,000
      J64,799,456456709,00050,0004,040,00020,000,00040,000,000
      N563,39853103,08320,00010,000530,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.