2 Replies Latest reply: Oct 11, 2013 10:00 AM by Johann Choucq RSS

    Slow Changing Dimension Sum

      I have data simlar to the following 2 tables:

       

      Sales                                                       Office

      Date        Sales    Person                         Person     Office     Start        End

      1/6/11       4          Bob                              Bob          DC       1/1/11     3/1/11

      1/12/11     5          Bob                              Bob          DC       2/3/11     2/8/11

      3/7/11       2          Bob                              Bob          NY       3/2/11     4/8/11

      3/10/11     5          Bob                              Bob          NY       3/5/11     4/2/11

                                                                        Bob          DC       4/9/11     7/1/12

       

       

      So Bob was in DC until 3/1, then on 3/2 moved to NY, then moved back to DC on 4/9/11.

       

      I need to sum up Bob's sales by office for DC and NY in total but also for various ranges, for example 2/1/11 - 4/10/11. 

       

      I assume using Aggr on Office, but for whatever reason I am missing something, probably obvious....!

       

      Help?

       

      THANKS!