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?