3 Replies Latest reply: Nov 16, 2012 11:08 AM by Jake Turrell RSS

    Previous Month Value

    974433
      Hi,

      I have a couple of accounts that use the previous month value across years. However, sometimes it's necessary update the value in the middle of the year and this change should be reflected in the following months and years mantaining the old value in previous months and years.

      Somenthing like this:

      Year - FY12
      Jan- 100
      Feb- 100
      Mar- 100
      Apr- 100
      May- 100
      Jun- 100
      Jul- 100
      Aug- 100
      Sep- 100
      Oct- 100
      Nov- 100
      Dec- 100

      FY13
      Jan- 100
      Feb- 100
      Mar- 100
      Apr- 100
      May- 100
      Jun- 100
      Jul- 100
      Aug- 100
      Sep- 100
      Oct- 100
      Nov- 100
      Dec- 100

      If I change the value in November->FY12 to 200, I want:
      Year - FY12
      Jan- 100
      Feb- 100
      Mar- 100
      Apr- 100
      May- 100
      Jun- 100
      Jul- 100
      Aug- 100
      Sep- 100
      Oct- 100
      Nov- 200
      Dec- 200

      FY13
      Jan- 200
      Feb- 200
      Mar- 200
      Apr- 200
      May- 200
      Jun- 200
      Jul- 200
      Aug- 200
      Sep- 200
      Oct- 200
      Nov- 200
      Dec- 200

      Any help?

      Thanks
        • 1. Re: Previous Month Value
          Jake Turrell
          Most of my applications have some sort of "Adjustment" dimension. It usually looks something like this:

          - Total Adjusted
          -- Base
          -- Adj

          All Actuals data and most input forms load data into the "Base" member. However, most calculations look at the "Total Adjusted" member. In your case, your @PRIOR member formulas would simply point to the "Total Adjusted" member. Drop some data in the "Adj" member, and it will carry forward.

          There are other benefits to this approach:

          - Developers can create separate adjustment buckets. With the appropriate security in place you can see which group adjusted what.
          - Developers can create "Override" members as well as "Adjustment" members.
          - Any member combination can be adjusted (as opposed to simply creating adjustment accounts).

          Hope this helps,
          - Jake
          • 2. Re: Previous Month Value
            974433
            Hi Jake, thanks for sharing your knowledge.

            I think I understand what you said, but i have some questions:

            - In your "Adjustments" dimension you have any kind of IF clause in order to fill "Total Adjusted"?
            IF "Adj" !== #missing THEN "Total Adjustments" = "Adj" ELSE "Total Adjustments" = "Base", or user will input the difference in "Adj" account and it will be summed or subtracted to "base" account?

            - You have different data forms to "Adj" and "Base" member, and a final form with "Total Adjusted" or the Adjusted dimension members are page filters in only one data form?

            Regards,
            Pedro
            • 3. Re: Previous Month Value
              Jake Turrell
              I typically just aggregate up the Base + Adj to get "Total Adjusted". No need for a formula, unless you want to introduce specific override functionality. (Where the override amount would ignore what's in "Base" and "Adj") You may be able to get away with making "Total Adjusted" dynamic calc. This will depend on a lot of different factors, but it's something you should consider.

              Sometimes I put my adjustments into a separate form. Sometimes they're on the same form. That really depends on business requirements.

              Let's say you don't want to make this a separate dimension. Then the next option is typically creating specific adjustment accounts. The accounts where you're carrying the balance forward would look to see if an adjustment has been made in one of these specific adjustment accounts, and if so, add that adjustment to the prior value. I tend to prefer a separate adjustment dimension as it offers more flexibility, but if you've already got a database built, adding another dimension can be painful.

              - Jake