The solution to “Very strange results from MDX Query”
It turns out that there was both a psychological component and a technical one. The sleuth who help me discover these issues was none other than my friend, former and occasionally current colleague, Scot Reagin.
- The technical issue: I was using FORMAT_STRING to format the numbers I was using as my test case (primarily for presentation convenience). It turns out that this formatting had enough of an impact to present rounding errors.
- The psychological issue: It turns out that the rounding errors affected the numbers that I was using to test my results but not the results themselves. In other words, I assumed that MonthProjection should match (MTD / PCComplete) after I exported these figures to a spreadsheet and performed equivalent calculations. However, since I introduced rounding errors in my test figures, the numbers I was using to test for a correct result were in fact incorrect. The MonthProjection figure in my original post was in actual fact, correct. I was trying to get a correct answer to match an incorrect test case, based on the assumption that the test case was perfect.
I assumed my testing code was perfect. << sigh >>
I still think that this method of forecasting the month is very slick. In fact today at the client I complimented the analyst that came up with it.
The good news – today I presented a prototype of this forecasting technique against production sales data. There are actually more refinements developed, and more to come, so the end result is pretty cool.
I am really fortunate to be able to work with smart and innovative people.
