Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hassansm
New 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

Tags (1)
6 Replies
MVP
MVP

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

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

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

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

MVP
MVP

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

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

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

 

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

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

Hi,

let me try to attached sample qvf for your reference.

iluilyas
New Contributor III

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

Attaching on behalf of Hassan