Terry Smith


I'm trying to create a Customer Count measure. Each customer has a open date and a close date in the Customer dimension, so I need to filter the count based on this date range. For each time period the count should include customers where Open Date is <= the current time period and CloseDate >= the current time period. The measure needs to work for any time granularity (years, months, etc.).

I think this query is close but it is returning a 0 count for each month:

With Member [Measures].[Customer Count] As
Count
(
Exists
(
[Customers].[Customer ID].Members,
Filter
(
[Customers].[Customer ID].[Customer ID].Members,

[Customers].[Customer Open Date].CurrentMember.MemberValue <= [Date].[Full Date].CurrentMember.MemberValue
AND
[Customers].[Customer Close Date].CurrentMember.MemberValue >= [Date].[Full Date].CurrentMember.MemberValue
)
)
)
Select
[Measures].[Customer Count] On Columns,
[Date].[Calendar Year Hierarchy].[Month] On Rows
From [Insight DW]
Where [Date].[Calendar Year].&[2006]

In this example [Date].[Full Date] is the raw DateTime value matching the key of the Date dimension.

If I replace the the two date compares in the second argument to Filter with "True" then the query returns the total row count of the dimension table. I'm assuming by this that I'm very close but just need to get my date compares right.

One problem I see is that since Full Date will always be a specific date I am probably comparing it wrong to [Customer Open Date].Current Member and [Customer Close Date].CurrentMember being that my Date granularity is months in this example (I'm selecting months on the rows of the query.)

How do I build a query like this and still keep if flexible so that it work whatever the date granularity is

Any help on this one would be VERY appreciated.

Thank you,





Re: Filtering on a date comparison in a dimension table

Deepak Puri


Assuming that .MemberValue returns a datetime value for the [Customer Open Date] and [Customer Close Date] attributes:

With Member [Measures].[Customer Count] As
    Count
   ( 
             Filter
             (
                [Customers].[Customer ID].[Customer ID].Members,

                [Customers].[Customer Open Date].CurrentMember.MemberValue <=

               Tail(Existing [Date].[Full Date].[Full Date]).item(0).item(0).MemberValue
                AND
                [Customers].[Customer Close Date].CurrentMember.MemberValue >=

               Head(Existing [Date].[Full Date].[Full Date]).item(0).item(0).MemberValue
          ) 
     )
Select
    [Measures].[Customer Count] On Columns,
    [Date].[Calendar Year Hierarchy].[Month] On Rows
From [Insight DW]
Where [Date].[Calendar Year].&[2006]







Re: Filtering on a date comparison in a dimension table

Terry Smith

Hi Deepak,

I greatly appreciate your help, and I hope you can spare just a couple of seconds to set me straight once more. Can you explain your use of Head and Tail above Also, are you passing just [Date].[Full Date].[Full Date] to the Existing function It seems to be missing a left parenthesis. Is there a period between "...[Full Date])" and "item(0).item(0)..." I've played around with it in Management Studio and tried the syntax in various forms and still can't get it.

Thanks again for all the great help!

Terry







Re: Filtering on a date comparison in a dimension table

Deepak Puri

Hi Terry,

Indeed, there is a period missing, which I've now added: it should be ".item(0).item(0)". Assuming that [Date].[Full Date].[Full Date] is the leaf level of the [Date] dimension key attribute, Existing [Date].[Full Date].[Full Date] returns the dates which exist with explicitly specified members of [Date] in the query (here, months). Head is used with [Customer Close Date], because the close date should presumably be >= the earliest date in the period selected. For example, if Sep.2006 is selected, any close date >= Sep.1, 2006. Likewise, the open date should presumably be <= the latest date in the period, in this case Sep.30. Of course, if close date < open date, all bets are off!






Re: Filtering on a date comparison in a dimension table

Terry Smith

Deepak, thank you once again for all your help.

I don't know if you can help anymore without having access to my database, but I'm still not getting results back. The exact query you gave me copied and pasted into Management Studio takes 1:36 to run and returns all zeros for the customer count. I've verified that my customer data is correct and that I should be getting counts > 0.

There must be something else I'm missing, but I don't know what.






Re: Filtering on a date comparison in a dimension table

Deepak Puri

Well, the first suspect would likely be the date comparisons - so I'll try to translate my query to the Product dimension of Adventure Works (which has Start Date and End Date attributes), and post the results...




Re: Filtering on a date comparison in a dimension table

Deepak Puri

Terry, here's a sample Adventure Works query - I verified a couple of the results with SQL queries of the DimProduct table:

>>

With Member [Measures].[ProductCount] As

Count

(

Filter

(

[Product].[Product].[Product].Members,

[Product].[Start Date].CurrentMember.MemberValue <=

Tail(Existing [Date].[Date].[Date]).item(0).item(0).MemberValue

AND

([Product].[End Date].CurrentMember is

[Product].[End Date].&[1899-12-30T00:00:00] or

[Product].[End Date].CurrentMember.MemberValue >=

Head(Existing [Date].[Date].[Date]).item(0).item(0).MemberValue)

)

)

Select

[Measures].[ProductCount] On Columns,

[Date].[Calendar].[Month] On Rows

From [Adventure Works]

Where [Date].[Calendar Year].&[2003]

-------------------------------------------------------------------------------------------------

ProductCount
January 2003 339
February 2003 339
March 2003 339
April 2003 339
May 2003 339
June 2003 339
July 2003 406
August 2003 406
September 2003 406
October 2003 406
November 2003 406
December 2003 406

>>






Re: Filtering on a date comparison in a dimension table

SkYneT54

Hello,

I'm trying to test this example and each count is at 406.

Does somebody manage to test this example

ProductCount
January 2003 406
February 2003 406
March 2003 406
April 2003 406
May 2003 406
June 2003 406
July 2003 406
August 2003 406
September 2003 406
October 2003 406
November 2003 406
December 2003 406

Thanks.





Re: Filtering on a date comparison in a dimension table

SkYneT54





Re: Filtering on a date comparison in a dimension table

Deepak Puri

Are you using AS 2005 Standard Edition - in the past, there have been differences between the Product dimension in Enterprise and Standard Editions You can try a similar query for the Promotion dimension instead:

>>

With Member [Measures].[PromoCount] As

Count

(

Filter

(

[Promotion].[Promotion].[Promotion].Members,

[Promotion].[Start Date].CurrentMember.MemberValue <=

Tail(Existing [Date].[Date].[Date]).item(0).item(0).MemberValue

AND

[Promotion].[End Date].CurrentMember.MemberValue >=

Head(Existing [Date].[Date].[Date]).item(0).item(0).MemberValue

)

)

Select

[Measures].[PromoCount] On Columns,

[Date].[Calendar].[Month] On Rows

From [Adventure Works]

Where [Date].[Calendar Year].&[2003]

-----------------------------------------------------------------------------------------------

PromoCount
January 2003 6
February 2003 6
March 2003 6
April 2003 6
May 2003 6
June 2003 7
July 2003 11
August 2003 11
September 2003 9
October 2003 6
November 2003 6
December 2003 6

>>






Re: Filtering on a date comparison in a dimension table

SkYneT54

Thanks for your help.

I use AS 2005 Standard Edition.

Indeed, this query """works""" for the promotion dimension.

The only difference between Product and Promotion is the date format. Indeed in Product dates are like that "2002-06-30 00:00:00" wherease in Promotion they are like that: "juin 30, 2002". The date of the Time dimension has the same format. So how can I do to compare dates whatever the format


Actually the figures aren't ok but it's better. Here are my results:

PromoCount

January 2003 1

February 2003 1

March 2003 3

April 2003 1

May 2003 3

June 2003 8

July 2003 10

August 2003 1

September 2003 3

October 2003 3

November 2003 3

December 2003 1

But if I watch the data of the dB, the good result should be yours.

Name StartDate EndDate

No Discount 2001-06-01 2004-12-31

Volume Discount 11 to 14 2001-07-01 2004-06-30

Volume Discount 15 to 24 2001-07-01 2004-06-30

Volume Discount 25 to 40 2001-07-01 2004-06-30

Volume Discount 41 to 60 2001-07-01 2004-06-30

Volume Discount over 60 2001-07-01 2004-06-30

Mountain-100 Clearance Sale 2002-05-15 2002-06-30

Sport Helmet Discount-2002 2002-07-01 2002-07-31

Road-650 Overstock 2002-07-01 2002-08-31

Mountain Tire Sale 2003-06-15 2003-08-30

Sport Helmet Discount-2003 2003-07-01 2003-07-31

LL Road Frame Sale 2003-07-01 2003-08-15

Touring-3000 Promotion 2003-07-01 2003-09-30

Touring-1000 Promotion 2003-07-01 2003-09-30

Half-Price Pedal Sale 2003-08-15 2003-09-15

Mountain-500 Silver Clearance Sale 2004-05-01 2004-06-30

If I try to display the result for all the dates, I can see that it's not so bad.

For example:

The dB contains:

Name Start Date End Date

Half-Price Pedal Sale 2003-08-15 2003-09-15

and the result is

...

aout (august) 14, 2003 0

aout 15, 2003 1

...

On the other hand, I don't understand why for example I have:

aout 31, 2003 1

septembre 1, 2003 3

septembre 2, 2003 2

septembre 3, 2003 2

septembre 4, 2003 0

To finish, the year isn't considered as you can see here:

PromoCount

July 2001 10

August 2001 1

September 2001 3

October 2001 3

November 2001 3

December 2001 1

January 2002 1

February 2002 1

March 2002 3

April 2002 1

May 2002 3

June 2002 8

July 2002 10

August 2002 1

September 2002 3

October 2002 3

November 2002 3

December 2002 1

...





Re: Filtering on a date comparison in a dimension table

Deepak Puri

Are you using SP2 - other than that, I can't reproduce any of the results you provided, so maybe someone with Standard Edition can help you.

"The only difference between Product and Promotion is the date format. Indeed in Product dates are like that "2002-06-30 00:00:00" wherease in Promotion they are like that: "juin 30, 2002". The date of the Time dimension has the same format. So how can I do to compare dates whatever the format " - since the sample query uses .MemberValue (which should be a datetime) for comparison, the format shouldn't matter.






Re: Filtering on a date comparison in a dimension table

SkYneT54

I use the AS project of the lesson 7 of the tutorial of Visual Studio BI.

http://msdn2.microsoft.com/en-us/library/ms166587.aspx

I use Standard Edition but I don't know if it includes the SP2.

I think I've found a reason to my date problem. Promotion and Time use a named calculation called "simple date".

Code Snippet
DATENAME(dd, FullDateAlternateKey) + ', ' +
DATENAME(yy, FullDateAlternateKey)

I think that it's not a DateTime and that explain the result but I don't manage to do query with a real datetime field like in the Product dimension.





Re: Filtering on a date comparison in a dimension table

Deepak Puri

When posting query results, please use the standard Adventure Works sample cube if possible, rather than projects from tutorial lesssons. Otherwise, reproducing results will be very difficult.




Re: Filtering on a date comparison in a dimension table

SkYneT54

Thanks a lot for your help.
I didn't know that there was a AS project with the tutorial.
Actually I've understood why it didn't work.
I've created my own time table and I've declared the type of my start date as a "DateStart" wherease than "Date".

Now I've another problem. I want to display all the records matching with the date dimension.
So I've created a named set :
Code Snippet

Filter
(
[Bon Vouchers].[N¡ã Bon Vouchers].[N¡ã Bon Vouchers].Members,
[Bon Vouchers].[Start Date].CurrentMember.MemberValue >=
CDate('2006-11-11')
Tail(Existing [DimDate].[Date].[Date]).item(0).item(0).MemberValue
)

But it doesn't work.
I don't know if a named set can be dynamic like that. How can I solve my problem !