How do I create a table of what cases were open at the end of a financial year?

[jcharles]

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.

[jcharles]

Also, just to clarify, the table I want to create is a straight table chart not a table in the script. Thanks

[datanibbler · Accepted solution]

Hi Joyce,

- as a primary dimension, you have your fiscal year, ok? (2010, 2011, 2012 ...)

=> Start by generating the last day of each, using MAKEDATE(), as a variable I'd suggest.

=> Use several expressions for "Open" and "Closed", with an IF_condition each, comparing the Closeddate to that
      end_of_fiscal_year AND (combined just so) querying the STATUS..

That would be my suggestion. There might be better or more elegant ones around, though.

Best regards,

DataNibbler

[jcharles]

Thank you, let me give it a try. I'll let you know

[#5]

Hi Joyce,

Can u Add Sample Data/Apllication For Better understanding

[jcharles]

Hi

I've attached a sample of what the data looks like. Does this help?

[#7]

on which date are you calculating Financial year

[jcharles]

On REPORTEDDATE

[#9]

hi joyce ,

please find Attachment ,

hope this helps

[jcharles]

Thank you for this, I do appreciate your help a lot but it still won't give me want I need.

If the year runs from 1 July to 30 June every year and a case has a closed status and was reported on the 02/10/2011 but the date closed was 01/08/2012 then as at 30/06/2012 this case would be Open even though the status is now closed. We cannot rely on what the status says as the status is what it is today and not what it was at the end of each financial year. So for this entry 2009,2010,2011,2013,2014 would be closed and not be counted, 2012 would show as open and be counted in the count even though the status now is closed.

E.g.

If we look at the first record with CASEID 1 on the attached then for 2009, 2013 and 2014 would be closed and not be counted, 2010, 2011, 2012 would be open and be counted in each financial year as an open case.

Financial Year 01/07/2008 - 30/06/2009      0

Financial Year 01/07/2009 - 30/06/2010     1

Financial Year 01/07/2010 - 30/06/2011     1    

Financial Year 01/07/2011 - 30/06/2012     1

Financial Year 01/07/2012 - 30/06/2013     0

Financial Year 01/07/2013 - 30/06/2014     0

Hope this clarifies