Qlik Sense - when a dimension filter data/report is blank

[iyub_sultan]

Hello Experts,

Needed your assistance to solve my issue.

Below is my Business requirement:

I need to calculate the productivity of each doctors. I have the scheduling data in one table .i.e. total hours worked by each employee and each of them might work in more than one task for a given day (not always). This multiple task  is given a "category" to distinguish between the tasks.

2nd table , I have the transaction table which contains the total no. of patients that doctor saw per day. This transaction table also have two , three dimensions (E.g Patient type, Patient New/ Established) which we use it to calculate the statistics.

Problem:

In my load script, I have created a join between these 2 tables based on 2 field: Doctor, Date.

In my App, i have plotted the stats as below:

Total patient seen per hour in Clinic:

Count of patients/ SUM(total hours) under category "Clinic" (Category = 'Clinic').

The problem is If I apply a filter on any of the dimension in the transaction table (Patient Type, New/Established) my Pivot chart / any chart used for Calculation goes blank or Null. To test the data I plotted a table Chart and found that IF those dimension are added along with the schedule data it shows as blank or Null.

As per my requirement, I wanted Qlik Sense to apply the filter 1st and then do the calculation. This will help me to to drill down and get to the statistics.

Can someone assist how to fix this problem?

- Sultan

[iyub_sultan]

Below is my load script:

//below is the Transaction table. The join is based on 2 fields: CLINICIAN, sch_date

LOAD

IPOP,

LAB_REC,

RAD_REC,

PHARM_REC,

SVC_CNT,

DR_STAFF_CATEGORY,

MNO,

DR_NO,

PAT_NO,

MED_DATE,

MED_DESC,

MED_TYPE,

ECLAIM_NO as CLAIM_ID,

PAT_VST_TYPE,

PAT_MED_TYPE,

UPPER(DR_NAME),

CLINICIAN,  //link between transaction to doctor schedule

UPPER(ORDERING_CLINICIAN),

date(MED_DATE,'YYYY-MM-DD') as 'Sch_date' //link between transaction to doctor schedule

     IF(PAT_VST_TYPE =1 and MATCH(MPT,1,3,8),'New Visit',IF(PAT_VST_TYPE =2 and MATCH(MPT,1,3,8),'Follow Up','N/A'))  as 'New/Follow';

[VISIT_SUMMARY_VW]:

SELECT A."IPOP",

A."LAB_REC",

A."RAD_REC",

A."PHARM_REC",

A."SVC_CNT",

A."DR_STAFF_CATEGORY",

A."MNO",

A."DR_NO",

A."PAT_NO",

A."MED_DATE",

A."MED_DESC",

A."MED_TYPE",

A."ECLAIM_NO",

A."PAT_VST_TYPE",

A."PAT_MED_TYPE",

A."DR_NAME",

A."CLINICIAN",

A."ORDERING_CLINICIAN"

FROM "HOSPITAL"."VISIT_SUMMARY_VW"  A,  "HOSPITAL"."PAT_MED_CLAIM_V3"  C WHERE  A.ECLAIM_NO = C.CLAIM_ID

AND C.INS_CNT > 0 AND IPOP IN ('OP', 'DC','OTHR')

AND A.CLINICIAN = 'GD20624';

//Doctor schedule table. This table has the total hours worked on a given date by a doctor

[Qlik_Appt_Schedule_Actual]:

LOAD

    Appt_Sch_ID,

   // Doc_Name,

    "LIC No"     as "CLINICIAN", //link to transaction table;

    date(Schedule_Date,'YYYY-MM-DD') as 'Sch_date', //link to transaction table;

    Tot_Hours as "Total Hours",

    Category,

    Sub_Category,

    Dept;

SQL SELECT

"Appt_Sch_ID",

//"Doc_Name",

"LIC No",

"Schedule_Date",

"Tot_Hours",

"Category",

"Sub_Category",

"Dept"

FROM "HISREPORT"."dbo"."Qlik_Appt_Schedule_Actual";