I have a seemingly simple requirement to create a query of price data that will return four sets of price data for different dates:
- The lowest date of the range
- The highest date of the range
- The aggregate sum for the entire range
The closest I have is the following query:
WITH set [Range] as {[Effective Date].[Date].&[2015-12-01T00:00:00] : [Effective Date].[Date].&[2015-12-31T00:00:00]}
select
NON EMPTY{
filter([Effective Date].[Date].&[2015-12-01T00:00:00], [Measures].[Plan Price]),
filter([Effective Date].[Date].&[2015-12-31T00:00:00], [Measures].[Plan Price]),
nonempty([Measures].[Money In], [Range]),
nonempty([Measures].[Money Out], [Range])
} on 0,
NON EMPTY{
[Region Manager].[Full Name].[Full Name]
} on 1
from [Cube]
However, the date ranges as such return the error:
Two sets specified in the function have different dimensionality.
The "filter" or "nonempty" statements will work individually but I cannot get them to work in a single query. How can I do this? Will it be helpful to use a sub query?
{([A],[B],[C]...),([A],[B],[C]...),([A],[B],[C]...) ...}
where A, B and C need to be the same hierarchies, in the same order. – whytheq