4 Replies Latest reply: Jun 23, 2015 9:24 PM by Sinan Ozdemir RSS

    Common Date Dim

    Srinivas Surabhi

      Hi All,

      I have 4 view/table which are connected to each other. In each view/table, has date field. Please refer to screenshot below:

      Now I want to create common date dimension (dates from all 3 tables) and generate report for each table based on selected Year/Month ...  For Ex: # of Activities in 2015, # of project in 2015

      I had refered to one the discussion  and implemented the below:

      DateLink:
      LOAD     

        CUSTOMER_NUMBER
      ,PROJECT_START_DATE as Date
      ,'Project' as DateType
      RESIDENT Project;
      LOAD         

        CUSTOMER_NUMBER
      ,SALE_DATE as Date
      ,'Contract' as DateType
      RESIDENT Contract;

      LOAD   

      CUSTOMER_NUMBER
      ,UPDATED_DATE as Date
      ,'Activity' as DateType
      RESIDENT Activity;

       

      CALL CalendarFromField('Date', 'CommonCalendar', '');

      Due to some reasons, results are incorrect. Data is not picked corretly

      Date.png

      Can anyone please advise, how this can be acheived?

        • Re: Common Date Dim
          Sinan Ozdemir

          I guess you were trying to create a master calendar and this is how I would do:

           

          Capture.PNG

            • Re: Common Date Dim
              Srinivas Surabhi

              Appologies, by mistake mark my question as Assumed Answered. As advised, I tried to create master calendar but Calender is not linked to any tables. What I need/expecting, bsed on Year/Month/Week selection from Master Calender, can able to generate reports for Activities/Contracts/Projects. Common calender should be used as selection/filter acroos all tables.

               

              Date Mapping:

              Activity.Updated_date = Calendar.Date

              Contract.Sale_date = Calendar.Date

              Project.Project_Start_date = Calendar.Date

               

              If I use DERIVE FIELDS FROM SALE_DATE,UPDATED_DATE, PROJECT_START_DATE USING Calendar, then

              Results are specific to appropriate tables. My requirements is, in one sheet, I have reports belongs to Activity, Contract, Project, all reports should affect according based on Master Calendar item.

               

              Can you please advise?

              Capture.PNG

                • Re: Common Date Dim
                  Sinan Ozdemir

                  Hi Srinivas,

                   

                  Let's try this way, change your 'DateLink' model to the below:

                   

                  Capture.PNG

                   

                  And just delete DateLink from the drop table statement.

                   

                  If I am not wrong, DateLink table will be joined to other tables on Customer_Number field and Calendar on Date field to DateLink table.

                   

                  Hope this helps

              • Re: Common Date Dim
                Stefan Wühl

                I believe your issue is due to the fact that your DateLink table is only linked by CustomerNumber to the other tables.

                 

                First, I would suggest to step back and evaluate if a common date field for all three date fields is really what you want.

                See also:

                Canonical Date

                 

                You can try to build a concatenated table consisting of most of the fields of the three tables (some can probably transferred to dimension tables), but this might imply other issues, when you select on specific fields values, thus excluding other rows from your aggregations.