0

I have a data-set with Account Names, Order IDs and Close Dates. I would like to see the number of days between orders, per customer, however Excel will not allow me to sort by date in my Pivot Table Values so that my Calculated Field works properly.

See example below to illustrate

Account Name    Close Date     Date Diff
Alice       
   74hde72hrg   29/01/2017  
   ery3yrtyhgf  29/01/2017  0
   fdg5rrg3tg3  18/05/2018  474
   fgj465df35y  26/05/2017  -357
   h6hdh54y4    19/04/2018  328
   rfhbswreyg   18/07/2018  90
Bob     
   436yrefg5y   19/04/2018  
   43grey43v    10/05/2017  -344
   54ufhg54y    12/07/2017  63
   sdg3vrf4f4   10/05/2017  -63
Jimmy       
   547feg4gsfd  20/07/2018  
   dfh5heafh5   11/01/2018  -190
   fh35qhrdah   16/01/2018  5
   fha4yfdhg3j  11/01/2018  -5
   fhjwq54jrd5  20/07/2018  190
   g53qyhry35   11/01/2018  -190
   j655hrhg315  20/07/2018  190

Note that the Dates are not in order, and I cannot find how to put them in order in the Pivot Table so that the calculated Field is accurate.

I can do the Difference calculation in the Raw data, however it will also show me the difference between dates associated to adjacent Account Names, which I don't want.

Any Ideas?

1 Answer 1

0

Convert your

Account Name    Close Date  Date Diff
Alice       
   74hde72hrg   29/01/2017  
   ery3yrtyhgf  29/01/2017  0
   fdg5rrg3tg3  18/05/2018  474
   fgj465df35y  26/05/2017  -357
   h6hdh54y4    19/04/2018  328
   rfhbswreyg   18/07/2018  90
Bob     
   436yrefg5y   19/04/2018  
   43grey43v    10/05/2017  -344
   54ufhg54y    12/07/2017  63
   sdg3vrf4f4   10/05/2017  -63
Jimmy       
   547feg4gsfd  20/07/2018  
   dfh5heafh5   11/01/2018  -190
   fh35qhrdah   16/01/2018  5
   fha4yfdhg3j  11/01/2018  -5
   fhjwq54jrd5  20/07/2018  190
   g53qyhry35   11/01/2018  -190
   j655hrhg315  20/07/2018  190

to

Owner   Account Name Close Date  Date Diff
Alice   74hde72hrg   29/01/2017  
Alice   ery3yrtyhgf  29/01/2017  0
Alice   fdg5rrg3tg3  18/05/2018  474
Alice   fgj465df35y  26/05/2017  -357
Alice   h6hdh54y4    19/04/2018  328
Alice   rfhbswreyg   18/07/2018  90
Bob     436yrefg5y   19/04/2018  
Bob     43grey43v    10/05/2017  -344
Bob     54ufhg54y    12/07/2017  63
Bob     sdg3vrf4f4   10/05/2017  -63
Jimmy   547feg4gsfd  20/07/2018  
Jimmy   dfh5heafh5   11/01/2018  -190
Jimmy   fh35qhrdah   16/01/2018  5
Jimmy   fha4yfdhg3j  11/01/2018  -5
Jimmy   fhjwq54jrd5  20/07/2018  190
Jimmy   g53qyhry35   11/01/2018  -190
Jimmy   j655hrhg315  20/07/2018  190

This table can be easily sorted - by "Owner" and then by "Close Date".

2
  • Doing so allows me to sort the Close Dates properly, but then my Difference calculation will also show the difference between Alice's last order and Bob's first order. I want the differences to be only calculated on a customer per customer basis. I thought of adding line breaks between customers, but my raw data has thousands of customers and I'm not sure how to automate that.
    – Sdieser
    Aug 14, 2018 at 15:42
  • my Difference calculation will also show the difference between Alice's last order and Bob's first order Pffff.. E2=IF(A2=A1,D2-D1,"").
    – Akina
    Aug 14, 2018 at 15:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .