+ Reply to Thread
Results 1 to 15 of 15

Gathering date difference from pivot table

  1. #1
    Registered User
    Join Date
    12-12-2018
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    9

    Gathering date difference from pivot table

    Hey everyone, This is my first post so unfortunately I cannot attach any links or images, hopefully I can describe it well enough.



    I have a pivot table, which comes from audit logs.

    The columns that represent different 'objects'.

    The '50%' and '75%' etc columns are different 'options' within an option set on the object.

    The '1' values simply show the that the option set was changed FROM this option on a date.



    My end goal is to have a table displaying the average amount of days an object spent at each option for every option . For example:

    10% | 3.4

    30% | 5.8

    50% | 7.9

    75% | 17.4

    ...

    It is also worth mentioning that the data is dynamic and over time, more objects and rows will be created.

    Thank you for reading, does anybody have an idea how I could achieve this? If someone is able to point me in the right direction I would really appreciate it.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Gathering date difference from pivot table

    If you have access to PowerPivot, yes, it is possible. Otherwise, you'd need to do aggregation in PQ I'd imagine.

    If you need specific help. I'd recommend you upload sample workbook, along with expected output.

    To upload file, use "Go Advanced" button, follow "Manage Attachments" hyperlink. This will launch new tab/window for managing uploads.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    12-12-2018
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Gathering date difference from pivot table

    Thank you very much for your response, I think I have attached two small sample files to help visualize the data.

    -There is only one object in the sample that data can be calculated from ( as the options show the old option label )

    I do have access to Power Pivot! Would you happen to know of any resources that you could recommend for my situation?

  4. #4
    Registered User
    Join Date
    12-12-2018
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Gathering date difference from pivot table

    This reply should contain the data?
    Attached Files Attached Files

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Gathering date difference from pivot table

    I need to see the raw data, not the pivoted data.

  6. #6
    Registered User
    Join Date
    12-12-2018
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Gathering date difference from pivot table

    Sorry about that, here it is

  7. #7
    Registered User
    Join Date
    12-12-2018
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Gathering date difference from pivot table

    Here it is*
    Attached Files Attached Files

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Gathering date difference from pivot table

    Ok, so you want to summarize based on opportunity ID?

    Your data isn't clear how each column relates.

    Ex:
    For opportunityid "a2323482-99a1-e811-8153-70106faac321" you have 4 lines. How should this be summarized?
    Do you want to [auditdate] where [oldvalue_label] = 50% (i.e. 9/18/2018) - [auditdate] where [oldvalue_label] = 30%? (i.e. 4.17?).

  9. #9
    Registered User
    Join Date
    12-12-2018
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Gathering date difference from pivot table

    One thing to note: I have filtered out duplicate [auditid] rows through excel's data tools, each [auditid] should be unique so I have cleaned that up in the data on my end.

    That's right,

    whenever the option set changes from one value to another, I would like the difference from the old row's [auditdate] to the new row's [auditdate].
    This would be the time spent against the second row's [oldvalue_label]

    Using your example "a2323482-99a1-e811-8153-70106faac321" (now containing 2 rows after removing duplicates):

    I can gather from this that the opportunity spent 4 days on '50%', before it was changed to ' '. What I would like at the end is the amount of days on average that an opportunity spends on each [oldvalue_label]

    I apologise if I'm not explaining the problem very well.

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Gathering date difference from pivot table

    Ok, I think I got your requirement now. I'm headed out for the night. Will take another look at it tomorrow.

  11. #11
    Registered User
    Join Date
    12-12-2018
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Gathering date difference from pivot table

    Thank you for your time, really appreciate it.

  12. #12
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Gathering date difference from pivot table

    Hmm, in this instance. Easiest method is to do self merge of data table.

    I've attached sample self merge with duration calculation in PowerQuery/Get & Transform.

    Key is sorting data (first by opportunityid and then by auditdate) and adding 1 based index to original query, and copying same query and adding 0 based index.

    However, I've got one more question. How should this data be summarized? (i.e. what column value should be row label?).

    And how should null duration be treated? (i.e. last record of each opportunityid).
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-12-2018
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Gathering date difference from pivot table

    This is brilliant, looks like I'll be aggregating the newvalue_label's average.

    I've had a look at it and I'm getting my head around it, I'll have a good look over it tomorrow and see if I can get it to work with the live query and see if I can get some answers.

  14. #14
    Registered User
    Join Date
    12-12-2018
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Gathering date difference from pivot table

    This has worked brilliantly,

    Rather than get&transform, I used Power BI to visualize the data and it works great. As I mentioned, it has been aggregated by the average duration per [newvalue_label] and null durations are ignored, as they might get changed in the future.

    Thank you very much for this.

  15. #15
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Gathering date difference from pivot table

    You are welcome

    If this solved your issue, please mark the thread as solved by using thread tool found at top of your initial post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 12-03-2017, 01:41 PM
  2. Pivot Table with % difference from
    By moty.98 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-14-2016, 04:55 AM
  3. Replies: 0
    Last Post: 02-25-2015, 01:02 PM
  4. How to compare % difference between date range through pivot table
    By ramkichn in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-09-2014, 09:12 AM
  5. Var difference on Pivot table
    By gico1972 in forum Excel General
    Replies: 5
    Last Post: 12-21-2010, 01:10 PM
  6. Calculate Date difference in pivot table
    By shinymcshires in forum Excel General
    Replies: 0
    Last Post: 12-16-2008, 02:46 PM
  7. [SOLVED] Pivot Table Subtotals - Difference
    By Marcotte A in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2005, 01:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1