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

[hassansm]

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 No	Authorized Date	Completion Date	Feed Back Date	Status
WR100001	15-Jan-18	25-Feb-18	1-Mar-18	Completed
WR100002	20-Jan-18	12-Mar-18	20-Mar-18	Completed
WR100003	24-Feb-18			Pending
WR100004	8-Mar-18			Pending

Regards,

Hassan

[sunny_talwar]

So, you want to show Feb when  March is selected only for Pending.... use set analysis... what kind of chart are you using to view this information? Do you have a sample qvf to help you better here

[hassansm]

Yes, for pending record the table should not consider filter selected in page, I want to display the details in table not in chart.

[sunny_talwar]

Right, which kind of table are you envisioning to see it in? Again, would you be able to share a sample qvf to help you further...

[hassansm]

Hi,

Based on the below given sample data, if I select the Month year filter as "Mar-2018"

Sr#	Work Request No	Authorized Date	Completion Date	Feed Back Date	Status
1	WR100001	15-Jan-18	25-Feb-18	27-Feb-18	Completed
2	WR100002	15-Jan-18	25-Feb-18	01-Mar-18	Completed
3	WR100003	20-Jan-18	12-Mar-18	20-Mar-18	Completed
4	WR100004	24-Feb-18			Pending
5	WR100005	08-Mar-18			Pending

I would like to get the below given output in table object.

Sr#	Work Request No	Authorized Date	Completion Date	Feed Back Date	Status
1	WR100002	15-Jan-18	25-Feb-18	01-Mar-18	Feedback Received
2	WR100003	20-Jan-18	12-Mar-18	20-Mar-18	Completed/Feedback Received
3	WR100004	24-Feb-18			Pending
4	WR100005	08-Mar-18			Pending

[hassansm]

Hi,

let me try to attached sample qvf for your reference.

[iluilyas]

Attaching on behalf of Hassan