topic Re: Count open issues per month in QlikView

Count open issues per month

Fri, 24 Jul 2015 09:14:17 GMT

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

Re: Count open issues per month

sibideepak — Fri, 24 Jul 2015 12:56:30 GMT

Hi ,

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

Re: Count open issues per month

Fri, 24 Jul 2015 13:06:34 GMT

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.

Re: Count open issues per month

stigchel — Fri, 31 Jul 2015 13:36:45 GMT

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);

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

Re: Count open issues per month

Tue, 04 Aug 2015 07:43:51 GMT

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

Re: Count open issues per month

cbaqir — Wed, 06 Jun 2018 17:29:18 GMT

I keep getting:

Script line error:

if(=0) then