Results 1 to 7 of 7

Thread: How to to combine multiple date dimensions - LinkMember?

Threaded View

  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unhappy How to to combine multiple date dimensions - LinkMember?

    MDX Newbie here.

    The facts in our cube are based around events, and an event can have these dates (among others) associated with it: LoggedDate, LastInvoiceRejectedDate, InvoiceClearedDate. The cube includes measures for CountInvoicesCleared and CountInvoicesRejected based on their related cleared/rejected dates. For each month, I'd like to view the percentage of invoice actions that are rejections ie

    Sum(events with LastInvoiceRejectedDate) x 100 / ( Sum(events with LastInvoiceRejectedDate) + Sum(events with LastInvoiceClearedDate) )

    My current query ties each item to the LoggedDate but invoices for an event logged in January could be rejected in Feb then cleared in March. How do I get the monthly counts to correlate in the correct monthly columns (eg an invoice cleared in March should count for March, irrespective of the LoggedDate)? Reading around it looks like LinkMember is a likely contender, but I don't see how to get it to work.

    Current query:

    WITH

    SET [KeyMonths] AS
    '[LoggedDate].[All].[2009].children'

    -- THESE GENERATE a "Syntax error in axis definition" ERROR
    --
    -- ('LinkMember([LoggedDate].[All].[2009],[InvoiceClearedDate].[All])).children'
    -- ('LinkMember([LoggedDate].[All].[2009],[InvoiceRejectedDate].[All])).children'


    SET [ThisYear] AS '[LoggedDate].[2009].[January]:[LoggedDate].[2009].[December]'

    SET [Performance] AS
    '{
    ([Measures].[Count Invoices Cleared]),
    ([Measures].[Count Invoices Rejected])

    }'
    SELECT
    [KeyMonths] ON 0,
    [Performance] ON ROWS
    FROM [MainCube]

    Using Analysis Services 2000.


    Cheers
    Colin
    Last edited by Dundonian; 03-22-2009 at 02:20 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •