topic Re: Issues left Open per Day (Opened before, closed After) in QlikView

Issues left Open per Day (Opened before, closed After)

salto — Mon, 10 Nov 2014 16:18:37 GMT

Hello,

I have a table on which I record the user issues / problems we take care of.

Issue ID	CreatedOn	SolvedOn
1	13/10/2014	14/10/2014
2	13/10/2014	17/10/2014
3	14/10/2014	16/10/2014
4	14/10/2014	17/10/2014
5	15/10/2014	16/10/2014

I need to add another column IssuesLeftOpen that shows the number of issues in a 'non solved' state, per day.

So:

for 13/10 the value would be 2 (issues 1 and 2)

for 14/10 it would be 3 (issues 2, 3 and 4)

for 15/10 it would be 4 (issues 2, 3, 4 and 5)

for 16/10 it would be 2 (issues 2 and 4)

for 17/10 it would be 0

DateID	IssuesLeftOpen
13/10/2014	2
14/10/2014	3
15/10/2014	4
16/10/2014	2
17/10/2014	0
18/10/2014	0

I have been trying several set analysis formulas with no luck. If anyone has an idea... thanks in advance!

Re: Issues left Open per Day (Opened before, closed After)

MK_QSL — Mon, 10 Nov 2014 16:32:24 GMT

Create below in your script

T1:

Load * Inline

[

Issue ID, CreatedOn, SolvedOn

1, 13/10/2014, 14/10/2014

2, 13/10/2014, 17/10/2014

3, 14/10/2014, 16/10/2014

4, 14/10/2014, 17/10/2014

5, 15/10/2014, 16/10/2014

];

NoConcatenate

T2:

Load [Issue ID], CreatedOn as DateID, 'Created' as Flag Resident T1;

Load [Issue ID], SolvedOn as DateID, 'Solved' as Flag Resident T1;

Drop Table T1;

=========================================

Create a Straight Table

Dimension

DateID

Expression

=RangeSum(Above(COUNT({<Flag = {'Created'}>}DateID),0,RowNo()))-RangeSum(Above(COUNT({<Flag = {'Solved'}>}DateID),0,RowNo()))

Go to Presentation Tab

Untick Suppress Zero Values

Re: Issues left Open per Day (Opened before, closed After)

salto — Tue, 11 Nov 2014 11:38:39 GMT

Phew! Very interesting approach, clever. Still it does not give me the exact values, but it is very ingenious (or at least it is for me).

Thanks a lot!

Re: Issues left Open per Day (Opened before, closed After)

MK_QSL — Tue, 11 Nov 2014 11:56:13 GMT

Let me know if this code is not giving exact values as per your sample ... Where you found wrong values

Re: Re: Issues left Open per Day (Opened before, closed After)

salto — Tue, 11 Nov 2014 12:25:09 GMT

I may not have explained myself correctly. The chart should show the total of Issues Created - Solved "by the end of that day":

for 13/10 result should be 2, because two issues have been created that day and none is solved
for 14/10,result should be 3: 2 more created and 1 resolved
for 15/10, 4: 1 more created, none solved
for 16/10: 2: none created, 1 solved
for 17/10: 0: 2 solved, none created.

But the results are as follows:

13/10: 1

14/10: 1

15/10: 2

16/10: 1

17/10:0

I attach the file with your suggestion.

Many thanks.

Re: Issues left Open per Day (Opened before, closed After)

salto — Tue, 11 Nov 2014 12:37:47 GMT

Hi Manish,

the correct expression for the chart is:

=RangeSum(Above(COUNT({<Flag = {'Created'}>} [Issue ID]), 0, RowNo())) -RangeSum(Above(COUNT({<Flag = {'Solved'}>} [Issue ID]),0,RowNo()))

(Replace DateID for [Issue ID])

Many thanks for your help. Now it works as requested.

Re: Issues left Open per Day (Opened before, closed After)

kunkumnaveen — Tue, 29 Dec 2015 10:41:04 GMT

HELLO Manish,if u get a chance will be plz explain how this set analysis is working out ,i mean wat above() function bringing out etc

=RangeSum(Above(COUNT({<Flag = {'Created'}>} IssueID), 0, RowNo())) -RangeSum(Above(COUNT({<Flag = {'Solved'}>} IssueID),0,RowNo()))