Wednesday, March 7, 2012
MDX Query for Nullable Date value
I have a table Facts.Policy in which there is a created date (required) and an issued date (not required). I have a Policy Count measure which counts all policies regardless of the issued date as I would expect. How would I create a custom measure to only get a count of Policies that have an issued date populated? There is a Date dimension ([Issued Date]). I have tried non empty with [Issued Date], and I am either using it incorrectly or that is not the right approach. Any guidance would be much appreciated.You could add a calculated column to your DSV that holds 1 if the Issued Date is populated, 0 if it is not. Then create a SUM measure on the new column. Not an MDX based solution, but it works, and it is fast.|||If issue date is a dimension and you are using SSAS 2005 then you must have configured an unknown member or the cube processing would be throwing errors. This means that all the null issue dates would be getting mapped to something like [issue date].[unknown] so in addition to John's solution you could also create a query that excluded the unknown member using the Except() function eg. EXCEPT([Issue Date].Members, {[Issue Date].[Unknown]})|||Yes, I believe the null processing was set to automatic. I may tweak with that a bit more and use your suggestion for the query. Thanks for pointing me in the right direction.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment