Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count open issues per month

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

De data set is like

IssueContributedFinished
A1-Jan-152-Jan-15
B10-Jan-1515-Jan-15
C14-Jan-153-Mar-15
D7-Feb-153-Apr-15
F9-Feb-15
G11-Mar-1512-Mar-15
H12-Mar-151-Apr-15
I21-Mar-1530-Jun-15

Issue F has not yet been implemented.

The result has to be like:

MonthOpenClosedIssues
Jan12C
Feb32C,D,F
Mar44D,F,H,I

Regards,

Marcel

1 Solution

Accepted Solutions
stigchel
Partner
Partner

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,',')

View solution in original post

5 Replies
sibideepak
Creator II
Creator II

Hi ,

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

Not applicable
Author

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

MonthOpenClosedIssues
Jan12C
Feb32C,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
Partner
Partner

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,',')

Not applicable
Author

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
Specialist II
Specialist II

I keep getting:

Script line error:

if(=0) then