|MDX-Why YDT function sometimes return incorrect results for measure with NON_EMPTY_BEHAVIOR?|
|Written by Vidas Matelis|
|Tuesday, 17 April 2007 19:49|
Q: Why YDT() function sometimes return incorrect results for measure with NON_EMPTY_BEHAVIOR defined property?
A: This question was answered by Mosha Pasumansky in MSDN forum thread:
NEB almost always cannot be used with YTD, because it is practically guaranteed to produce wrong results. Let's consider the following example:
Now, why is it wrong to define NON_EMPTY_BEHAVIOR=Sales on YTDSales measure. Let's suppose that we have following data:
What would be the result of Query
Obviously we want to have just January and February. But in the following query
We want to get all of the months. Now, defining NEB of YTDSales to be that of Sales will result in the second query returning only January and February. This is obviously wrong.
Feb 27, 2008: Read here workaround example posted by Chris Webb.
- How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project
- MDX-How can I get Last (Previous) Year to Date (YTD) values?
- What TCP port SQL Server Analysis Services 2005 uses
- MDX-How do you calculate monthly average of one year, optionally including empty months?
- MDX-How do I calculate sales for 12 Month to date?