0

I am quite new to MDX and I need to write a query that gives me the day difference between two dates. The problem is that the dates exist on two different rows in my data. For example:

My Fact table:

SEAL | STARTDATE | PROCESS | FK_DATE_KEY  
1    | 2012-10-22|    A    | 20121022
1    | 2012-10-24|    B    | 20121024
2    | 2012-10-22|    A    | 20121022
2    | 2012-10-26|    B    | 20121026

What I need returned is :

SEAL | AGE_IN_DAYS
1    |      2
2    |      4

Please help.... I have a date dimension that relates to my FK_DATE_KEY

LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
Sheldon Shane Govender
  • 13
  • 6

1 Answers1

1

If you are new to MDX, you shouldn't try to do this problem using only MDX. This particular problem is much easier if you write it under SQL and use that data in your Analysis Services.

So, the easiest and nicest way to do this problem is to write a view which returns the same data you gave in your question. (SEAL | AGE_IN_DAYS) Then you are able to insert these data in your Data Source View (if you choose 'new named query', you can fetch table-valued functions too, not only views and tables)

Hope it helps!

user1758851
  • 111
  • 3
  • Thanks a lot! I another solution is to add the startdate as a float to the Fact table, then in my report I just use the lookup function between the two data sets (one for each process) and do a date difference on the floats casted as dates. Thanks a lot, much appreciated! – Sheldon Shane Govender Oct 24 '12 at 08:12