WHERE Date >= '20010101'

WHERE Date >= '20010101'

Post by Mikha » Wed, 04 Dec 2002 00:22:46



My cube has [Date] dimension that consists of three levels - year,
month,day.

1)How do I specify the slicer condition if I want to aggregate only
values with dates more or equal or more than some date? For example >=
'20010101'

2)Is filter the only solution to query for a range of dates, which
start or end values do not exist as members of [Date] dimension. For
example if I want [Date] BETWEEN [Date].[2001].[1].[1] AND
[Date].[2002].[1].[1], and either of these two members does not exist
then my [Date].[2001].[1].[1]:[Date].[2002].[1].[1] query will fail
with error...

Regards,
Misha.

 
 
 

WHERE Date >= '20010101'

Post by Krisztian Pint » Wed, 04 Dec 2002 18:21:48


Quote:> 1)How do I specify the slicer condition if I want to aggregate only
> values with dates more or equal or more than some date? For example >=
> '20010101'
> 2)Is filter the only solution to query for a range of dates, which
> start or end values do not exist as members of [Date] dimension.

No straightforward solution. You can refer to the names of items such

[Date].CurrentMember.Name > "20010101"

so you can use a filter like

Filter([Date].[Day].Members, [Date].CurrentMember.Name > "20010101")

and the final MDX is

WITH MEMBER [Date].[Range] AS 'Sum(Filter([Date].[Day].Members,
[Date].CurrentMember.Name > "20010101")'
SELECT
...
WHERE
  [Date].[Range]

but it works only if the date has the above numeric format, since
month names, for example, are not in alphabetical order.

With more complex date formats, you can put a member property, and
store the "nice" date format in it. Then you can filter on that
property:

Filter([Date].[Day].Members, [Date].CurrentMember.Properties("Nice") >
"20010101")

The same concept goes for closed ranges.

 
 
 

1. Change date '1993' to '2003


use convert ( char, dateadd ( year, 100, issdat,112). The 112 makes
the conversion use the format YYYYMMDD

__________________________________________________

http://www.xs4all.nl/~reinoud
I specifically DO NOT give anyone permission to use my email adress
for any commercial or non commercial mailings. I will bill everyone
who sends me this kind of mail for wasting my time. Under Dutch law,
people who don't let me know they disagree with such a bill are obliged
to pay it.
Attention: remove extra anti-spam info at end of reply adress

2. Activex Databases

3. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

4. Job Opening in Massachusetts

5. Constraint for orderdate to be >today's date

6. Converting from NT Server 4.0 to Server 2000

7. 'RETURN'ing > 512

8. Test score

9. >Run-Time Error Message '91'

10. Problem with: DBI->data_sources('Informix')

11. 'Use <database>' overhead

12. <'>'s

13. >= 8 Q's on complext QBE's