Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
salto
Specialist II
Specialist II

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

Hello,

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

Issue ID

CreatedOn

SolvedOn
113/10/201414/10/2014
213/10/201417/10/2014
314/10/201416/10/2014
414/10/201417/10/2014
515/10/201416/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

DateIDIssuesLeftOpen
13/10/20142
14/10/20143
15/10/20144
16/10/20142
17/10/20140
18/10/20140

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

View solution in original post

6 Replies
MK_QSL
MVP
MVP

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

salto
Specialist II
Specialist II
Author

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!

MK_QSL
MVP
MVP

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

salto
Specialist II
Specialist II
Author

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.

salto
Specialist II
Specialist II
Author

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.

kunkumnaveen
Specialist
Specialist

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