Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
See attached qvw
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) |
|
|
CD1300000292734 |
|
CD1300000292734 |
|
Here the data should be 6.81 only. 21.93 not to be count.
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.
TICKETNUMBER | WORKSTATUS | MODIFIEDON |
| |||
CD1300000292734 | Pending for Customer Approval | 20/05/13 | Pick Minimum Date | 20/05/13 | Maximum Date - Minimum Date | |
CD1300000292734 | Pending for Customer Approval | 21/05/13 | ||||
CD1300000292734 | Pending for Customer Approval | 23/06/13 | ||||
CD1300000292734 | Customer Approved | 01/06/13 | Pick Maximum Date | 01/06/13 | ||
CD1300000292734 | Pending for Customer Approval | 29/05/13 | Pick Minimum Date1 | 29/05/13 | Maximum Date1 - Minimum Date1 |
|
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 |
|
Need help on the above example.