Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

aveeeeeee7en
Not applicable

Calculate Date Difference for Various Status against 1 Number

Need help on this :

I have the data against this Ticket Number :

TICKETNUMBER

WORKSTATUS

MODIFIEDON

CD1300000304725

Pending for Customer Approval

10-07-13

CD1300000304725

Customer Approved

11-07-13

CD1300000304725

Customer Approved

11-07-13

CD1300000304725

Pending for Customer Approval

11-07-13

CD1300000304725

Pending for Customer Approval

11-07-13

CD1300000304725

Pending for Customer Approval

24-07-13

CD1300000304725

Pending for Customer Approval

26-07-13

CD1300000304725

Customer Approved

12-08-13

CD1300000304725

Pending for Customer Approval

13-08-13

CD1300000304725

Customer Approved

13-08-13

What exactly i need :

TICKETNUMBER

WORKSTATUS

MODIFIEDON

Cases to Pick

Sum (Total)

CD1300000304725

Pending for Customer Approval

10-07-13

Min1

Max1-Min1 = 1 Day

CD1300000304725

Customer Approved

11-07-13

CD1300000304725

Customer Approved

11-07-13

Max1

CD1300000304725

Pending for Customer Approval

11-07-13

Min2

Max2-Min2 = 30 Days

CD1300000304725

Pending for Customer Approval

11-07-13

CD1300000304725

Pending for Customer Approval

24-07-13

CD1300000304725

Pending for Customer Approval

26-07-13

CD1300000304725

Customer Approved

12-08-13

Max2

CD1300000304725

Pending for Customer Approval

13-08-13

Min3

Max3 - Min3 = 0 Day

CD1300000304725

Customer Approved

13-08-13

Max3

TOTAL

1 + 30 + 0 = 31 Days

  

To Pick Minimum Ticket Number and Maximum Creation date not only once but number of times if Multiple status appear against same Ticket Number

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Not applicable

Re: Calculate Date Difference for Various Status against 1 Number

See attached qvw


talk is cheap, supply exceeds demand
5 Replies
Gysbert_Wassenaar
Not applicable

Re: Calculate Date Difference for Various Status against 1 Number

See attached qvw


talk is cheap, supply exceeds demand
aveeeeeee7en
Not applicable

Re: Calculate Date Difference for Various Status against 1 Number

Thank You very much

aveeeeeee7en
Not applicable

Re: Calculate Date Difference for Various Status against 1 Number

There are some lag in the logic.

ROW

TICKETNUMBER

WORKSTATUS

MODIFIEDON

REFDATE

39457

CD1300000292734

Pending for Customer Approval

29/05/13

29/05/13

39450

CD1300000292734

Pending for Customer Approval

30/05/13

29/05/13

40138

CD1300000292734

Pending for Customer Approval

03/06/13

29/05/13

4630

CD1300000292734

Customer Approved

05/06/13

29/05/13

373

CD1300000292734

Pending for Customer Approval

17/06/13

17/06/13

2603

CD1300000292734

Pending for Customer Approval

17/06/13

17/06/13

5373

CD1300000292734

Pending for Customer Approval

19/06/13

17/06/13

6326

CD1300000292734

Pending for Customer Approval

22/06/13

17/06/13

7144

CD1300000292734

Pending for Customer Approval

26/06/13

17/06/13

6948

CD1300000292734

Pending for Customer Approval

01/07/13

17/06/13

3528

CD1300000292734

Pending for Customer Approval

04/07/13

17/06/13

7666

CD1300000292734

Pending for Customer Approval

09/07/13

17/06/13

For above case it should calculate only the Highlighted data but it is Calculating others also.

Since i need both Pending for Customer Approval and Customer Approval if they both are coming so pick max and min dates.

Highlighted data is correct since it has both Pending for customer approval and Customer Approved Dates but below after this there are Pending for Customer Approval multiple cases but there is no Customer Approval Status. So. we dont want to need these dates. But According to you Calculation it is calculating its difference also.

TICKETNUMBER

max(MODIFIEDON)-only(REFDATE)

  1. 28.74

CD1300000292734

  1. 6.81

CD1300000292734

  1. 21.93

Here the data should be 6.81 only. 21.93 not to be count.

swuehl
Not applicable

Re: Re: Calculate Date Difference for Various Status against 1 Number

You can try with a reversed order of your rows, so you only set a refdate when there is Approval.

I used ROW for reverse sorting your records, but maybe you are using a timestamp with your real data, which would be more appropriate that a plain date value for multiple status entries.

aveeeeeee7en
Not applicable

Re: Calculate Date Difference for Various Status against 1 Number

TICKETNUMBER

WORKSTATUS

MODIFIEDON

  1. 11.85318
CD1300000292734Pending for Customer Approval20/05/13Pick Minimum Date20/05/13Maximum Date - Minimum Date
CD1300000292734Pending for Customer Approval21/05/13
CD1300000292734Pending for Customer Approval23/06/13
CD1300000292734Customer Approved01/06/13Pick Maximum Date01/06/13
CD1300000292734Pending for Customer Approval29/05/13Pick Minimum Date129/05/13Maximum Date1 - Minimum Date1
  1. 6.8136
CD1300000292734

Pending for Customer Approval

30/05/13

CD1300000292734

Pending for Customer Approval

03/06/13

CD1300000292734

Customer Approved

03/06/13

Pick Maximum Date1

CD1300000292734

Customer Approved

05/06/13

05/06/13

CD1300000292734

Pending for Customer Approval

17/06/13

Not to be used since after 'Pending for Customer Approval' there is no 'Customer Approval'. So, I don’t have to consider these cases.

CD1300000292734

Pending for Customer Approval

17/06/13

CD1300000292734

Pending for Customer Approval

19/06/13

CD1300000292734

Pending for Customer Approval

22/06/13

CD1300000292734

Pending for Customer Approval

26/06/13

CD1300000292734

Pending for Customer Approval

01/07/13

CD1300000292734

Pending for Customer Approval

04/07/13

CD1300000292734

Pending for Customer Approval

09/07/13

Total

  1. 18.6667

Need help on the above example.