Count open issues per month

[#1]

I need an object that displays the number of open issues per month.

De data set is like

Issue	Contributed	Finished
A	1-Jan-15	2-Jan-15
B	10-Jan-15	15-Jan-15
C	14-Jan-15	3-Mar-15
D	7-Feb-15	3-Apr-15
F	9-Feb-15
G	11-Mar-15	12-Mar-15
H	12-Mar-15	1-Apr-15
I	21-Mar-15	30-Jun-15

Issue F has not yet been implemented.

The result has to be like:

Month	Open	Closed	Issues
Jan	1	2	C
Feb	3	2	C,D,F
Mar	4	4	D,F,H,I

Regards,

Marcel

[sibideepak]

Hi ,

Can you elaborate on what basis open & closing is calculated ..since i'm little confused.

[#3]

The calculation is done by month

so in every month the calculation (counting Issues that are open) is done on the Issues.

if you look at the values in february the output would be

Month	Open	Closed	Issues
Jan	1	2	C
Feb	3	2	C,D,F

since at that point only the records of those months are known. Because Issue C is not ready in Jan it is counted in that month but in february it still isn't finished and therefore again its counted.

[stigchel · Accepted solution]

Don't know if this still helps, but please find attached an example.

There maybe more efficient ways of loading this, but this is what I came up with.

//Load Script

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Data:

Load * Inline [Issue,Contributed,Finished

A,01/01/2015,02/01/2015

B,10/01/2015,15/01/2015

C,14/01/2015,03/03/2015

D,07/02/2015,03/04/2015

F,09/02/2015,

G,11/03/2015,12/03/2015

H,12/03/2015,01/04/2015

I,21/03/2015,30/06/2015

];

//Determine status

Month:

CrossTable (Status,Month,1) LOAD

Issue,

if(len(Finished)>0,Month(Finished)) as Closed,

if(Month(Finished)>Month(Contributed) or len(Finished)=0,Month(Contributed)) as Open,

if(Month(Finished)>Month(Contributed),Month(Finished)) as ClosedLater

resident Data;

//determine max month for not yet closed issues

LET m=Num(Month(Peek('Finished',-1,'Data')));

//loop through issues and adding them to each month in which they are not closed

Let n = NoOfRows('Data');

FOR i=1 to $(n)

  LET T1=Num(Month(Peek('Contributed',$(i)-1,'Data')));

  LET T2=Num(Month(Peek('Finished',$(i)-1,'Data')));

  LET x=$(T2)-$(T1);

  //for the month itself

  if($(x)=0) then

  SET x=1;

  //not yet closed issues

  ELSEIF $(x)<0 then

  LET x=$(m)-$(T1);

  ENDIF

  FOR j=1 to $(x)

  Issues:

  Load

  if(Month(Peek('Finished',$(i)-1,'Data'))>Month(Peek('Contributed',$(i)-1,'Data')) or len(Peek('Finished',$(i)-1,'Data'))=0,Month(AddMonths(Peek('Contributed',$(i)-1,'Data'),$(j)-1))) as Month,

  if(Month(Peek('Finished',$(i)-1,'Data'))>Month(Peek('Contributed',$(i)-1,'Data')) or len(Peek('Finished',$(i)-1,'Data'))=0,Peek('Issue',$(i)-1,'Data')) as Issues

  resident Data where RecNo()=$(i);

  NEXT

Next

//Expression in chart for open cases (with full accumulation)

Count({<Status={'Open'}>} TOTAL <Month> Issue)-

Count({<Status={'ClosedLater'}>} TOTAL <Month> Issue)

//Expression in chart for Closed cases (with full accumulation)

Count({<Status={'Closed'}>} Issue)

//Expression in chart for Issues

=Concat(DISTINCT Issues,',')

[#5]

Great script Piet Hein,

I was already started by using IntervalBetween but didn't manage to do the trick you created.

Thx for your effort and solution!!

Marcel

[cbaqir]

I keep getting:

Script line error:

if(=0) then