This discussion is archived
3 Replies Latest reply: Nov 16, 2012 9:08 AM by Jake Turrell RSS

Previous Month Value

974433 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points