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

[salto]

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!

[MK_QSL · Accepted solution]

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]

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]

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

[salto]

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]

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]

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