Using a different dimension in a chart

jcharles — Mon, 25 Aug 2014 14:15:07 GMT

Hi everyone

I have a table and a log table for cases reported in the business as follows:

CASETABLE:

LOAD

CASEID,

CASESTATUSID,

STATUSDESCRIPTION,

BRANCHJSYSCODE,

BRANCHNAME,

DATEREPORTED,

DATECLOSED

FROM CASETABLE;

CASELOGTABLE:

LOAD

CASETRANFERLOGID,

CASEID,

CASEID AS XFRDCASE,

PREVIOUSBRANCH,

NEWBRANCH,

DATETRANSFERRED,

APPLYMAP('BRANCHMAP',PREVIOUSBRANCH,'0') AS PREVBRANCHNAME,

APPLYMAP('BRANCHMAP',NEWBRANCH,'0') AS NEWBRANCHNAME

FROM CASETRANSFERLOG;

The log table does not have a case if no transfer took place from one branch to another branch so I fix this by doing the following:

LOAD

CASEID&'AUTO' AS CASETRANSFERLOGID,

CASEID,

CASEID AS XFRDCASE,

BRANCHJSYSCODE AS PREVIOUSBRANCH,

BRANCHJSYSCODE AS NEWBRANCH,

DATEREPORTED AS DATETRANSFERRED,

APPLYMAP('BRANCHMAP',BRANCHJSYSCODE,'0') AS PREVBRANCHNAME,

APPLYMAP('BRANCHMAP',BRANCHJSYSCODE,'0') AS NEWBRANCHNAME

RESIDENT CASETABLE

WHERE NOT EXISTS (XFRDCASE,CASEID);

LEFT JOIN

LOAD

CASEID,

MIN(DATETRANSFERRED) AS FIRSTDATE,

MONTH(MIN(DATETRANSFERRED)) AS FIRSTMONTHREPORTED

RESIDENT CASETRANSFERLOG

GROUP BY CASEID;

All of this works. On my chart though I have a dimension called BRANCHNAME and an expression as follows:

Expression 1 - ({<CASESTATUSID = {"1","6"}>}CASEID)

This works as it gives me a count of all cases with a status of 1 or 6 per branch.

The problem I have is with my second expression. I want a count of cases per the branch they were reported in originally and not where they are now:

Expression 2 - IF(PREVBRANCHNAME = BRANCHNAME AND FIRSTMONTHREPORTED = $(=vCURRENTMONTH) , COUNT(DISTINCT(CASEID)) , BRANCHJSYSCODE) - This does not work at all

or I use

Count ( DISTINCT {< PREVBRANCHNAME ,[FIRSTMONTHREPORTED] = {'$(=vCURRENTMONTH)'} , [REPORTED MONTH] = {'$(=vCURRENTMONTH)'} >} CASEID ) - This gives me the branch where the case is currently lying and not where the case was reported (which should be the previousbranch).

Although the dimension is BRANCHNAME, the column to give me the number of cases reported should be the previousbranch.

I hope I'm explaining myself properly. My chart has a dimension but in one of the columns I need to use a different dimension.

How do I do this?

Thank you.

topic Using a different dimension in a chart in QlikView

Using a different dimension in a chart