Essbase

Get Involved. Join the Conversation.

Topic

    Dzhao
    Rolling 3 month sum
    Topic posted October 2, 2019 by Dzhao Red Ribbon: 250+ Points, tagged Essbase 
    28 Views, 6 Comments
    Title:
    Rolling 3 month sum
    Content:

    Hi There,

    We have dimensions, Years, Periods (time dimension) with Quarter - Month, Account, etc. I would like to create rolling 3 month sum, for example, currently it is Oct, rolling three month sum would be Aug+Sep+Oct, so I create member RollSum under Period, then I am trying to use MovSum in the following formula as test

    @MOVSUM ("Net Income",3, "Jan":"Jun");

    However it comes back without any value when I retrieve from SmartView, any pointer and advice on where I did wrong? or is there better way to do this?

    Thanks

    Comment

     

    • Tim Faitsch

      I'd change the formula to use "Jan":"Dec". Does it give you a value for June?

    • Dzhao

      Tim,

      Thank you so much for your reply. I actually tried this

      @MOVSUM("Total Expense",3, "Jan":&curMonth);

      it seems working, for example, it is return sum of total expense for Aug + Sep and Oct. However I am stuck on this

      1. I am trying to say that I want to rolling 3 month for all descendants of Net Income, so I change to @MOVSUM(@Descendants("Total Expense"),3, "Jan":&curMonth); so user can get 3 month rolling sum for whatever account they drill down to, however the one I have above is not working somehow, any advice on what I did wrong here?

      2. When it is crossing year, for example, Feb of 2020, the rolling sum of 3 month should be Dec of 2019 + Jan + Feb, and my formula not returning any value.

      Any pointers or new approach is appreciated.

       

       

    • Tim Faitsch

      I wouldn't use a substitution variable for the month. You can just name all of the months.

      1. If you put this formula on an account, you can only get the moving sum for a single account (otherwise how would Essbase know which account you wanted?). If you want to have a generic moving sum for all accounts (or some accounts) then you need to put it into a different dimension. I can't see your outline so I don't know what makes sense.

      2. You will need the @XRange() function for your third argument if you want it to cross years. Something like this: 

      @XRANGE("2018"->"Jan", "2020"->"Dec")
      
    • Dzhao

      Tim,

      Thanks again. I have Account, Years, Period (Quarter-Month), and CostCenter, and my member formula (RollSum) is under Period (time dimension), so in this case, what should I put in my first parameter for MovSum if I want to apply to all account?

       

    • Dzhao

      One more question, is there any reason that I should not use substation variable? users want to have rolling three month sum, so my thinking is that to make it dynamic.

      Also I test on @MOVSUM("2018",3, "Jan":&curMonth); it will apply to all account (chart of account), however this will limit to one year, 

    • Tim Faitsch

      The question really comes down to how flexible you want this to be. If you just want the moving sum of the last three months then you don't need to use @MOVSUM. Just add the members up in a formula: "Aug"+"Sep"+"Oct";. You could easily cross years with a cross dim operator. You could also drive it using substitution variables. That's probably the simplest way of doing this.

       

      But what if someone is looking at a prior year report? What if they want to compare Oct moving sum versus Oct moving sum from last year or Sep moving sum? You've got a few options:

       

      1. Add a "View" dimension. Load data into one stored member, add a moving sum dynamic member. This will be the most flexible as you can make it work for all accounts and all time periods. If the cube is already in production this is probably the least attractive option.

      2. Add a moving sum member for each of the 12 periods in the period dimension. I don't love this one, but it is an option.

      3. Add a moving sum member in the accounts dimension for each account that needs it. If the list of accounts that really need this analysis are small then this is a good option. But what happens when the users want moving average as well? Six month moving sum? etc...

       

      For crossing years, the @XRange function works great. If you're hard coding three months then you might have to use an if statement to handle January and February to grab the prior year for November and December.