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

[dellzy]

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

 

[DILIPandey]

Hi dellzy,

I wish to track period when the old value change to new value, but just for specific items

Lets say old value is 50 and new value is 80, so do you want to capture when 80 comes in place of old value (replaces 50) ?

and what you want to capture ... system date & time when this change happens ? or some other thing ?


Regards,
DILIPandey

 

[dellzy]

Hi dellzy,



Lets say old value is 50 and new value is 80, so do you want to capture when 80 comes in place of old value (replaces 50) ?

and what you want to capture ... system date & time when this change happens ? or some other thing ?


Regards,
DILIPandey

Dear DILIPandey ,


Firstly, thank you for your prompt 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. Judging from your reply, you briefly understood what I need but please allow me to give you the sample data as per attached along with details here for much better understanding.


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.

https://www.dropbox.com/s/ex0wboekponw2io/Pivot-CalculatedField.xlsx?dl=0



DZ

 

[DILIPandey]

Hi Delizy,

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.

What is your expected layout (which you are trying to achieve through pivot) ?
Also, you can use dynamic defined names (powered with offset and counta functions) to create a dynamic pivot table.

Regards,
DILIPandey

 

[dellzy]

Hi,
I want to be able to show 2 things. The count of times an individual attend the particular ticket and how much tine he/she spend on each assigned. The layout is not a matter as long as the objective is achieved.
Can you show me how,please? As I really don't know anymore of other ways to show the required result.

Thank you in advance.

DZ

 

[DILIPandey]

I want to be able to show 2 things. The count of times an individual attend the particular ticket and how much tine he/she spend on each assigned.

Okay.. see sheet 1, green cell in below workbook :-

https://dl.dropboxusercontent.com/u/65227334/Pivot-CalculatedField.xlsx

Regards,
DILIPandey

 

[dellzy]

Okay.. see sheet 1, green cell in below workbook :-

https://dl.dropboxusercontent.com/u/65227334/Pivot-CalculatedField.xlsx

Regards,
DILIPandey

Hi,

Really appreciate your hard effort on this one. I saw your formulas and it sure gives me some good leads to have a return value on assigned date (MIN) and resolved date (MAX). But I notice that this is not generated directly in the pivot table so the output will not follow when I filter the items in pivot table. Is there anyway to put this in pivot table so that the user can dynamically change the position of the items/fields and still should get same result.

I hope you understand my explanations.

Thank you in advance.

DZ

 

[DILIPandey]

Hi DZ,

yes.. it is a challenge.. one way could be to use the dynamic range in max and min formulas and these ranges will expand / squeeze based on the filtered data. But it needs data sorted on Employee and Tickets.
If looks fine, you can search internet for dynamic ranges and replace existing ranges from max / min formulas with dynamic range names.


Regards,
DILIPandey