Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hassansm
Contributor II
Contributor II

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

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

6 Replies
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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

 

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
Contributor II
Contributor II
Author

Hi,

let me try to attached sample qvf for your reference.

iluilyas
Contributor III
Contributor III

Attaching on behalf of Hassan