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:
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!
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.
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!
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
Have you read through any identical weblogs within the issue?
This post definitely is practical and i’m glad that you just wrote it.
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!!
This paragraph will assist the internet visitors for building up new blog or
even a blog from start to end.
Ultimately, garage floor cleaning works something
like this.
Your mode of explaining the whole thing in this post is genuinely nice, all be
able to effortlessly understand it, Thanks a lot.