MDX-number of "days" between two (time dimensions)

Question

MDX-number of "days" between two (time dimensions)

pahernand

Old Hand

Points: 338
More actions
October 14, 2003 at 12:45 pm

#151949

What is the syntax for counting the number of "days" between two dates (time dimensions)? I want to show the number of days between when a request was created to when it was completed?
Here's code I'm using (referencing "MDX Solutions" by George Spofford), but not having much luck, just errors.
Two Time Dimensions(YQMD)
[Create Date].[Day] & [Complete Date Query].[Day]
----------------------------------
WITH Member [Measures].[DaysBetween] AS
'{[Create Date].[Day] : [Complete Date Query].[Day]}.Count'
SELECT
{[Measures].[DaysBetween]} ON COLUMNS,
{[Request ID].[Request Id]} ON ROWS
FROM [Test]
Appreciate any help!!!
Paul
October 17, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Hey Paul,

I think this is possibly not working because you are asking to count members starting in one dimension and ending in a second.

All I can think of doing is trying to use something like linkmember to get the 'same' member in DIm1 as is selected in Dim2 (ie if [Complete date query].currentmember = [1999].[Q1].[Jan 99].[31 jan 99], then try to use linkmember (or another function?) to get the same date but from the [Create Date] dimension). After doing this (ie getting two members from the same dimension) you should be able to count the number of members in the set.

HTH,

Steve.

Edited by - stevefromOZ on 10/21/2003 12:30:30 AM

Steve.

pahernand Old Hand Points: 338 More actions · Answer 2

Steve,

thanks for your response! Unfortunately, I'm new to MDX and not sure how to implement your suggestion? Could you possibly tell me the steps required, syntax, etc?

Thanks again,

Paul

hoosierpride Mr or Mrs. 500 Points: 565 More actions · Answer 3

Steve is correct. You cannot create a set that spans two dimensions. His linkmember idea is excellent. Here's an example for using the LinkMember function that will return the equivalent date in the Create Date dimension.

LinkMember([Complete Date].currentmember,[Create Date])

So, what you need is...

{[Create Date].currentmember:LinkMember([Complete Date].currentmember,[Create Date])}

This set should work in your calculated member.

A couple of things to keep in mind using this construct.

1. The keys have to be the same in both dimensions. For example, if you are using dates (1/1/2001) as your key in Create Date it must be the same in Complete Date.

2. This is member based, so you will need to use actual members or member functions to build the set using the LinkMember function.

Hope that helps.

Steve Hughes

Magenic Technologies