Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Did you mean:
Not applicable

## Need Urgent Help...Calculation Between Rows

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

Regards

Siddharth

11 Replies

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
MVP

Your description is not clear.

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

Not applicable
Author

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

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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
MVP

Where is 1_C in this example?

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

Please provide proper information.

Not applicable
Author

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

Not applicable
Author

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

MVP

Where is 1_C here?

You are confusing now...!!!

Although 1_B and 1_C is closed

Not applicable
Author

Hi Manish,

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