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.
