1 Reply Latest reply: Jul 1, 2013 1:16 PM by user13279946 RSS

    Reporting on table with multiple dates.


      I've tried several different ways to work this out, and I'm stuck. Hoping for some help.


      I have a table containing three Dates that require reporting. Here is my table:

      CustomerIDLoan OfficerMet_DateApplied_DateApproved_Date
      1234Joe Smith201305012013050520130520
      1235Joe Smith20130505
      1236Jane Doe201305052013051020130515


      I also have a typical time dimension:  Date, Week, Month, Quarter, Year.



      My goal is to produce this report (Count of all of these things by Date and Loan Officer):

      DateLoan OfficerCustomers MetCustomers AppliedCustomers Approved
      20130501Joe Smith100


      Joe Smith110
      20130505Jane Doe100
      20130510Jane Doe010
      20130515Jane Doe001
      20130520Joe Smith001


      This is eluding me how I would add up all of these.  I've tried creating copies of my time dimension table in the physical table.  However, this seems to not work every time I add in Loan Officer.


      Can someone help me figure out how to structure this in my physical and logical model?