Have been using Microstrategy 9 for a while, had a moving sum problem recently. The customer basically needs moving sum for the past 12 months for each state/branch. Of course the month is based on accounting period, i.e. 1 May to 30th Apr each year. Below is the structure:

movingSum

 

 

For example for branch aaa, for May 2012, it should be 100, for June 2012, it should be 200, till 1200 for Apr 2013; For branch bbb, May 2012 figure should be 102(start from beginning) 204 for June, 1224 for Apr 2013.

The moving sum metric was movingSum<SortBy= (Month) >(revenue,12). When this metric was put on the report, it turned out the moving sum is always a cumulative sum of revenue of each branches first than by month, i.e. for branch bbb, it was 202 instead of 102 and  for branch aaa June 2012, it was 730 instead of 200.

Searched a lot on Google, thought about options like metric level(dimensionality), sort order, etc but none of them fixed the problem. At last after reviewing the SQL generated by Microstrategy, the correct formula should be movingSum<BreakBy{state,branch} SortBy= (state,branch,Month) >(revenue,12) which means for new state/branch, it starts from beginning, within each row, gets the moving sum.

Lessons learnt: Microstrategy trying to be user friendly by hiding technical (SQL) details, at price of using a lot of terms(such as target, group, filter, level) to build the system. In the case of this example, it’s far less obvious and far more complex than the original SQL solution!

 

 

Microstrategy movingsum
Tagged on:             

7 thoughts on “Microstrategy movingsum

  • May 26, 2014 at 3:30 am
    Permalink

    For hottest information you have to pay a quick visit web and on world-wide-web I found this site as a best web site for most recent updates.

    Reply
  • June 4, 2014 at 1:07 pm
    Permalink

    Hello there! This blog post couldn’t be written any better!
    Looking at this post reminds me of my previous roommate!

    He always kept talking about this. I most certainly will forward this article to
    him. Fairly certain he’s going to have a very good read.
    I appreciate you for sharing!

    Reply
  • June 5, 2014 at 6:07 pm
    Permalink

    You really make it appear so easy with your presentation however I in finding this topic to be actually
    something which I think I might by no means understand.
    It seems too complicated and very vast for me.
    I am having a look ahead in your next publish, I’ll try to get
    the hold of it!

    Also visit my blog post; rice cooking

    Reply
  • June 12, 2014 at 9:45 am
    Permalink

    Have you read through any identical weblogs within the issue?

    This post definitely is practical and i’m glad that you just wrote it.

    Reply
  • June 24, 2014 at 6:47 am
    Permalink

    Hello, i think that i saw you visited my blog so i came to “return the favor”.I’m attempting to find things to enhance my site!I suppose its ok to use some of your ideas!!

    Reply
  • August 17, 2014 at 1:50 pm
    Permalink

    Your mode of explaining the whole thing in this post is genuinely nice, all be
    able to effortlessly understand it, Thanks a lot.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

− 6 = 1

This site uses Akismet to reduce spam. Learn how your comment data is processed.