I have cases that are reported , worked on and then closed. The table has a CASEID, REPORTEDDATE , STATUS and CLOSEDDATE. I have set up the financial years based on the reporteddate in my script as follows:
TODAY()-ROWNO() AS REPORTEDDATE,
YEAR(ADDMONTHS(TODAY()-ROWNO(),6)) AS “REP FIN YR”
Status can either be "OPEN" or "CLOSED" but it will always reflect the status as at today.
I now want to create a table that will give me financial years and what was open at the end of each financial year.
e.g.
| FINANCIAL YEAR | NUMBER OPEN AT THE END OF THE YEAR |
|---|
| 2010 | 6 210 |
| 2011 | 41 178 |
| 2012 | 17 759 |
| 2013 | 12 540 |
The logic should be:
if the CLOSEDDATE is after the end of the financial year then the case would be OPEN as at the end of that financial year;
if the CLOSEDDATE is before the beginning of the financial year then the case would be CLOSED;
if the CLOSEDDATE is during the financial year then the case would be CLOSED;
if the STATUS is OPEN then the case is OPEN.
I used [REP FIN YR] as a dimension and then atempted to create another dimension with if statements and checking the "Suppress when value is null". My expression is count(distinct(CASEID)) but I'm not getting it right. Please can I get some help?
Thanks.
