6 Replies Latest reply: May 8, 2018 2:01 AM by Mohamed Ilyas Pitchai Mohamed RSS

    how to create the single dimension using multiple expression based on canonical calendar

    Mohamed Hassan

      Hi,

       

      I have table with three date columns (Authorized Date, Completed Date, Feedback date),based on these date column I have created
      canonical calendar and added the Month Year field as a filter in a page from conical calendar.

       

      how to show the pending request details including Feb'2018 request,(as per below given sample). if I select the monthyear filter as "Mar-2018" .

      For Completed request, the table should show only the request completed during Mar-2018.

      For Feedback, the table should show only feed back received during Mar-2018.

       

      how to create the single dimension for all the above scenario?.

       

       

      DateBridge:

         Load WRID, AuthorizedDate as WRCommonDate, 'Authorized' as DateType

                Resident WORK_REQUEST_DETAIL;

         Load WRID, CompletionDate as WRCommonDate, 'Completed' as DateType

                Resident WORK_REQUEST_DETAIL;

         Load WRID, FeedbackDate as WRCommonDate, 'Feedback' as DateType

                Resident WORK_REQUEST_DETAIL;

       

      MasterCalendar:
      Load distinct WRCommonDate,
           Month(WRCommonDate) as Month,
           Year(WRCommonDate) as "Year",
           date(monthstart(WRCommonDate), 'MMM-YYYY') as "MonthYear"
           Resident DateBridge;

       

      Sample data

         

      Work Request NoAuthorized DateCompletion DateFeed Back DateStatus
      WR10000115-Jan-1825-Feb-181-Mar-18Completed
      WR10000220-Jan-1812-Mar-1820-Mar-18Completed
      WR10000324-Feb-18 Pending
      WR1000048-Mar-18 Pending

       

      Regards,

      Hassan