Do I need to elaborate ???

You should be able to use the VBA DateDiff() function:

http://msdn.microsoft.com/library/de.../en-us/dnvbade
v/html/performingsimplecalculations.asp
For example, to calculate the number of hours that occurred between two
date variables, dtmValue1 and dtmValue2, you could write an expression
like this:

DateDiff("h", dtmValue1, dtmValue2)
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak.

Actually I want a Dimension member called "Current Date", which will always
show me current day's report.
I tried to create a member using "strToMember" and "Now()" in proclarity
6.0, and it works well upto current week. The "Time" table has Ids for all
members like year (2003, 2004 etc) month (1 to 12), but for day the row id is
used (it's an identity column), I can't change the design. The tables first
row starts with 1st jan 2003, so using datediff(d,"2003-1-1",Now()) i should
get the current day's id.
MSAS 2000 allows me to create set, but "datediff" gives error, i tried using
vba! but in vain. Even proclarity gives error with "DateDiff"

Can u suggest something.
by the way, the url is not working, shows "page not found"

Thanks

kshitij.

"Deepak Puri" wrote:

This Foodmart MDX query seems to work with datediff():

'datediff("d","2003-1-1",Now()) '
select {[Measures].[DateKey]} on columns
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,
Thanks.
This doesn't help me.
I am not good in MDX and this syntax is puzzling me.
What's the "DateKy" ? , I assume u have given it for foodmart2000, so this
is not something i need to replace with something else.
I tried to use this as "Set" of MSAS and also as a "member" in proclarity
(with foodmart2000 sales), but gives error on the DateDiff part.

Can I request u to elaborate on the syntax u have used ???

regards,,

kshitij.

"Deepak Puri" wrote:

This is standard syntax for a calculated member - the link below
explains more. Did the query return a result?

http://www.databasejournal.com/featu...le.php/3077641
...
The WITH operator in an MDX query creates a dynamic calculated member;
that is, the member, and therefore the calculation that the member
performs, only exists during the runtime life of the query.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

When I try to run the DateDiff in a mdx query my result set returns 0, when
I would of expected a number of some kind. I'm trying to get the number of
days between two
dates. Now, I'm not that surprised that this returned 0 because it doesn't
know what fact table I'm using because the two dates are in the dimension
table. It's not like it's calculating the difference between to numeric
values. I think I'm using the wrong approach here. Any suggestions???
This is my mdx query.

With Member [Measures].[NumofDay] as
'( datediff("d",[Vw Dim Service Date].[Service Sql Date],
[Vw Dim Receive Date].[Receive Sql Date]) )'
select {[Measures].[NumofDay]} on columns
from PBG_DW_Charge_Tran_Details

When I try to run the DateDiff in a mdx query my result set returns 0, when
I would of expected a number of some kind. I'm trying to get the number of
days between two
dates. Now, I'm not that surprised that this returned 0 because it doesn't
know what fact table I'm using because the two dates are in the dimension
table. It's not like it's calculating the difference between to numeric
values. I think I'm using the wrong approach here. Any suggestions???
This is my mdx query.

With Member [Measures].[NumofDay] as
'( datediff("d",[Vw Dim Service Date].[Service Sql Date],
[Vw Dim Receive Date].[Receive Sql Date]) )'
select {[Measures].[NumofDay]} on columns
from PBG_DW_Charge_Tran_Details

Could you describe your cube and dimensions in more detail; and are you
using AS 2000 or AS 2005?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I'm using AS 2005. In my cube I have two fact tables and several dimensions.
My date dimensions in the cube are actually views (vwDimServiceDate and
vwDimReceiveDate)based off of one Date Dimension (a real table). So it
"looks" like I have several Date dimensions, but I only have one. The point
here is that I want to create a calculated member on my AS2005 cube that
will simply calculate the number of days between one date (service date) and
another date ReceiveDate (one date coming from one date dimension-- which is
a view, and another date coming from another date dimension-- which is a
view)? I would think it would work simplly because I have a key in my fact
table that corresponds to each date, SK_ReceiveDateId and SK_ServiceDateId.
I hope you can help, I'm just feeling really furstrated and confused.
Thanks so much,
Mag