Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
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
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
Creator II
Creator II

Hi ,

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

Not applicable

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.

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

Not applicable

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

Specialist
Specialist

I keep getting:

Script line error:

if(=0) then