Need Urgent Help...Calculation Between Rows

[#1]

Hi Team,

I have one table ,

Date             CustomerId     ErrorCode          Status

12/1/2016            A                       1              UnResolved

12/2/2016            A                       2              UnResolved

12/2/2016            A                       3              UnResolved

12/3/2016            B                      1               UnResolved

12/3/2016            B                       2              UnResolved

12/3/2016            B                       3              UnResolved

12/2/2016            A                       1              Resolved

12/3/2016            A                       2              UnResolved

12/3/2016            A                       3              UnResolved

12/3/2016            A                       2              Resolved

12/3/2016            A                       3              UnResolved

I have few queries

How can calculate number of Unresolved CustomerIds (where Status is Unresolved) ..in above example, Cutomer id A is unresolved because m error Code 3 for Customer id A is still unresolved,

How to create cycle time, for resolved Error, for example Cutomer Id A and error 1 is resolved on 12.2.2016 so my Cycle time is 12/2/2016-12/1/2016+1

How can I calculate Aging for Unresolved cases

Thanks for your help in advance

Regards

Siddharth

[Anil_Babu_Samineni]

Try like below and if not, Can you provide expected table to look w.r.to Expecting?

1) Count({<Status = {"UnResolved"}>}Status)

2) If(Status = 'Resolved', Count({<Date = {Interval(Only({<Status = {'Resolved'}>}Date) - Only({<Status = {'UnResolved'}>}Date))}>} Status),Count({<Status = {"UnResolved"}>}Status))

[MK_QSL]

Your description is not clear.

Do you want to count number of CustomerID whose status is Unresolved at the max date?

[#4]

Thanks Anil for your kind reply.

Unfortunately Same Customer Id and Same Code can have multiple entries as Unresolved over several dates

Please find here with more specific table

Id	Code	FileDate	Status
1	A	12/1/2016	UnResolved
1	B	12/2/2016	UnResolved
2	A	12/2/2016	UnResolved
1	A	12/2/2016	Resolved
1	B	12/3/2016	UnResolved
1	B	12/4/2016	UnResolved
2	A	12/4/2016	UnResolved
2	A	12/5/2016	Resolved
Total No of Id		2	(1 and 2 are distict Ids)
Total Error		3	(1_A,1_B,2_A)
Total Open Errors		1	(1_B is still not Resolved)
Total Open Id		1	Although 1_B and 1_C is closed, but 1_A is still unresolved so overall 1 is still open
Closed Id	1	Id=2 is closed
Cycle Time for error		for 2_A, it is =5-2+1

Regards

Siddharth

[Anil_Babu_Samineni]

In your table, You are showing A and 3. But now table was different. Can you clear on this to make understand. What was the expected output

Can you send Excel with the data with Output, Please?

[MK_QSL]

Where is 1_C in this example?

Total Open ID = 1... 1_A not resolved? How???

Please provide proper information.

[#7]

Hi Anil,

Yes you are right, this one is new table , I have simplified it

Total No of Id		2	(1 and 2 are distict Ids)
Total Error		3	(1_A,1_B,2_A)
Total Open Errors		1	(1_B is still not Resolved)
Total Open Id		1	Although 1_B and 1_C is closed, but 1_A is still unresolved so overall 1 is still open
Closed Id	1	Id=2 is closed
Cycle Time for error		for 2_A, it is =5-2+1

this one is my expected output from below mentioned table

Id	Code	FileDate	Status
1	A	12/1/2016	UnResolved
1	B	12/2/2016	UnResolved
2	A	12/2/2016	UnResolved
1	A	12/2/2016	Resolved
1	B	12/3/2016	UnResolved
1	B	12/4/2016	UnResolved
2	A	12/4/2016	UnResolved
2	A	12/5/2016	Resolved

Regards

Siddharth

[#8]

Hi Manish,

Thanks for noticing it, Id 1 has 2 errors, if any one of it is still open then we consider id as open.

Think Id as Client Id, if any of his reported error is still not resolved, we will consider ticket (Customer Level..in this case Id ) as Unresolved

Note: I created new table so that I can show exact requirements

Regards

Siddharth

[MK_QSL]

Where is 1_C here?

You are confusing now...!!!

Although 1_B and 1_C is closed

[#10]

Hi Manish,

I am sorry,its my fault I changes table and hence no I_C in new table.

Can you please help me with one thing.

Can you help me flag these 2 last columns of these tables,

Id	Code	FileDate	Status	Flag Where FIleDate is Minimum for that Id and Code Combination	Flag where DateFIle is Max for that Id and Code Combination
1	A	12/1/2016	UnResolved	1	0
1	B	12/2/2016	UnResolved	1	0
2	A	12/2/2016	UnResolved	1	0
1	A	12/2/2016	Resolved	0	1
1	B	12/3/2016	UnResolved	0	0
1	B	12/4/2016	UnResolved	0	1
2	A	12/4/2016	UnResolved	0	0
2	A	12/5/2016	Resolved	0	1

Regards

Siddharth