Calculated Field - difference between value at current row and below row

Dear Experts,

I have a pivot table which data source is audit trails. In the first column of the pivot table will hv the series of dates, second col is old value and the third col is new value. I wish to track period when the old value change to new value, but just for specific items, which made me think to apply the formula in the pivot instead of in the original data table. So, is it possible to have an additional field that will return value of the period between date1 in the current row and date2 in bottom row in column Date?

Thank you in advance.

DZ

Originally Posted by Pepe Le Mokko

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi,
Thank you for the quick response.

Sorry for the delays in responding as I was trying very hard to try on my own and at the same time to built a mock data for better clarification and your kind perusal.

The attached file consists of 3 tabs of worksheets. "Data" tab consists of the raw data that we use to populate the pivot table in "pivot" tab. In data tab I highlighted some rows. "Members" is a namedrange where the values are as follows:-
1. MyGroup
2. User1
3. User2
4. User3

Originally the data exported from audit trails are all from column A until column G. In column H & I have some formulas to return the values based on criterias as below:-
1. Value in column "New Value" is always "MyGroup" (as per formula in column I)
2. Value in column "Old Value" always match either one in the "Members" namedrange EXCEPT "MyGroup" (as per formula in column I)

Actually the purpose of this is to track how many times a ticket is being assigned to MyGroup and each staff in MyGroup and how much man days is used to attend the case which is calculated from the datetime the case goes into MyGroup bucket, until the case is signed out from individual bucket.

From col H, I identified the datetimes involved. When I do the pivot table (pls refer to "pivot" tab), I filter the blanks for field "Assigned&Responded", it will display those involved dates chronologically. So, I set it to show me the difference between those dates (next datetime minus prev datetime) and it shows me the duration correctly in the grand total. However, what I need is these durations must be shown under the individual column so that the report reader can know how much the duration for him/her to attend the case. But I have tried so many ways but to no avail.

So, would appreciate it very much if someone with better expertise to come out with a solution. This pivot table should be dynamic for other ticket numbers in future and will be the main content as a report to management in terms of SLA achievement.

Thank you in advance.

DZ