SAP BW Training Forums BW305 – BEx Query Design and Analysis Moving window sum functions

Moving window sum functions

Back to course
Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #6700

    Vlad Bach
    Participant

    Hi Rob,

    There is one request I’m struggling to build – aggregate data, but based on moving window.

    To simplify:

    E.g. I have source data (values for years, some years might be missing):

    Source

    And I need to have sliding window sum of last 3 years and also moving window sum of last 2 years + current one, something like:

    Result

     

    Where for example: for 2017:

    60 = 30+20+10

    110 = 20 + 10 + 80

    however, for 2010 (while 2009 is missing – only sum of 2 years is taken 2007 + 2008 and 2008 + 2010):

    155 = 70 + 85

    135 = 85 + 50

    Any hint will be much appreciated! Thank you.

     

    #6702

    Rob Davis
    Keymaster

    Hi Vlad,

    I understand what you’re looking to accomplish.  Unfortunately BEx isn’t very flexible.  It requires all of the data be present for calculations like the one you’re looking to accomplish.  A workaround may be to add the missing years with a corresponding value of 0.

    Thanks,

    Rob

     

    #6704

    Vlad Bach
    Participant

    > It requires all of the data be present for calculations

    Not sure I fully understood this statement, while all required data for calculations is present.

    I don’t want missing years to appear in the result set (e.g. 2009 is missing in both source data and result set). I just want algorithm to work correctly, e.g. to look at last 3 calendar years.

    My main question was actually how to define such a rule. Is there any formula for this, or how this filtering (previous 3 years) could be set up?

    Thanks!

    • This reply was modified 4 years ago by Vlad Bach.
    #6706

    Rob Davis
    Keymaster

    Hi Vlad,

    I’m not aware of a way to just focus on a calendar year range rule within a calculation.  That’s why I mentioned pulling in the missing years to satisfy the requirement, but if that’s not an option I understand.  I wish I could be of more help on this but these types of calculations are outside the scope of the BW305 training.

    Thanks,

    Rob

Viewing 4 posts - 1 through 4 (of 4 total)
  • You must be logged in to reply to this topic.
top
Copyright © 2016 BUSINESS INTELLIGENCE SOLUTIONS LLC (This website is not affiliated with, sponsored by, or approved by SAP AG)