Calculating Time Between Dates in a Pivot Table

[jabu]

Hello

I am trying to calculate and then chart the difference between dates in a pivot table and I could use some help.

I have a list of dated transactions for various customers and I am trying to find the time elapsed since the previous payment so that I can show it in a stacked column chart. There could be any number of payments for a given customer and multiple payments could be received on the same day. Also each payment is labeled ("#1", "#2", "Deposit", "Prepayment", etc) and I'd like those labels to be reflected in the legend of the chart.

I am trying to do this with pivot tables because I am working with imported Quickbooks data. I want to be able to paste a huge list of transactions from QB that has all the info I need, then simply click refresh on my pivot tables and everything updates. I already have a bunch of other pivot tables and charts I've put together that work like this without me needing to clean/process the data in any way.

I'm sure I need to use Values with Show Values As "Difference From" but I cannot figure out how to get it to work. The values in the table always end up as either N/A, 1, or the date itself. I've tried tons of variations of different Base Fields and Base Items and I can't figure it out.

For example, if Customer A payment #1 received 1/1/21, payment #2 received 1/11/21, then payment #3 received 1/26/21, then there would be a stacked column chart with a column for Customer A and that column would have two segments, one of value 10 (labeled "payment #2") and the next of value 15 (labeled "payment #3").

Does that make sense? Ultimately I'm trying to get a visualization showing: this is how long each customer/project took and this is how long it was between payments for each customer.


Here are some example transactions:

Date	Customer	Memo	Amount
3/9/2021	Customer C	Draw #5	26339
4/14/2021	Customer J	Draw #1	21447
4/23/2021	Customer D	Draw #1	10263
5/18/2021	Customer H	Draw #5	16948
5/24/2021	Customer J	Draw #2	28596
6/21/2021	Customer D	Draw #2	13684
6/21/2021	Customer J	Draw #3	28596
6/23/2021	Customer H	Draw #6	11299
8/25/2021	Customer D	Draw #3	13684
9/9/2021	Customer J	Draw #4	28596
10/12/2021	Customer D	Draw #4	13684

Here is what my pivot table currently looks like:

income and expenses TEST.xlsx
	A	B	C	D	E	F	G	H
3	Sum of Date	Column Labels
4	Row Labels	Draw #1	Draw #2	Draw #3	Draw #4	Draw #5	Draw #6	Grand Total
5	Customer A	10/14/2021	11/23/2021	1/25/2022	2/28/2022	4/1/2022	4/8/2022	6/13/2632
6	Customer B	3/3/2022	4/14/2022	5/19/2022	6/22/2022	7/25/2022		11/21/2511
7	Customer C					3/9/2021		3/9/2021
8	Customer D	4/23/2021	6/21/2021	8/25/2021	10/12/2021	11/16/2021	1/14/2022	2/23/2630
9	Customer E	10/22/2021	11/15/2021	2/1/2022	3/8/2022	5/13/2022	5/13/2022	9/13/2632
10	Customer F	3/21/2022	4/14/2022	5/24/2022	7/1/2022			5/29/2389
11	Customer G	12/8/2021	3/12/2022	3/31/2022	5/11/2022	7/18/2022	7/18/2022	11/5/2633
12	Customer H					5/18/2021	6/23/2021	11/10/2142
13	Customer I	12/28/2021	2/4/2022	2/28/2022	4/20/2022	5/22/2022	7/1/2022	6/16/2633
14	Customer J	4/14/2021	5/24/2021	6/21/2021	9/9/2021	11/16/2021	12/30/2021	9/26/2629
15	Customer K	7/1/2022						7/1/2022
16	Grand Total	1/13/2997	7/10/2875	6/26/2876	6/22/2877	6/17/2998	3/19/2755	3/19/7880
Sheet1

Fields:

Which creates a stacked column chart that is almost what I want except that the vertical axis should be # of days not raw date.

Any help would be greatly appreciated.

 

[jorismoerings]

Hi,

Take a look if this is what you're looking for.

LOOKUP Salary By Year.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Date	Customer	Memo	Amount	Delta Days			Sum of Delta Days	Column Labels
2	9-3-2021	Customer C	Draw #5	26339	-			Row Labels	Draw #1	Draw #2	Draw #3	Draw #4	Draw #5	Draw #6	Grand Total
3	14-4-2021	Customer J	Draw #1	21447	-			Customer C					0		0
4	23-4-2021	Customer D	Draw #1	10263	-			9-mrt					0		0
5	18-5-2021	Customer H	Draw #5	16948	-			Customer D	0	59	65	48			172
6	24-5-2021	Customer J	Draw #2	28596	40,00			23-apr	0						0
7	21-6-2021	Customer D	Draw #2	13684	59,00			21-jun		59					59
8	21-6-2021	Customer J	Draw #3	28596	28,00			25-aug			65				65
9	23-6-2021	Customer H	Draw #6	11299	36,00			12-okt				48			48
10	25-8-2021	Customer D	Draw #3	13684	65,00			Customer H					0	36	36
11	9-9-2021	Customer J	Draw #4	28596	80,00			18-mei					0		0
12	12-10-2021	Customer D	Draw #4	13684	48,00			23-jun						36	36
13								Customer J	0	40	28	80			148
14								14-apr	0						0
15								24-mei		40					40
16								21-jun			28				28
17								9-sep				80			80
18								Grand Total	0	99	93	128	0	36	356
19
20
21
22	Sum of Delta Days	Column Labels
23	Row Labels	Draw #1	Draw #2	Draw #3	Draw #4	Draw #5	Draw #6	Grand Total
24	Customer C					0		0
25	Customer D	0	59	65	48			172
26	Customer H					0	36	36
27	Customer J	0	40	28	80			148
28	Grand Total	0	99	93	128	0	36	356
29
30
31
32
33
34
35
36
Sheet3

Cell Formulas
Range	Formula
E2:E12	E2	=IFERROR(DATEDIF(LOOKUP(2,1/($B$1:$B1=B2),($A$1:$A1)),A2,"D"),0)

this setup will utimately create this Stacked Bar graph.

 

[jabu]

Thank you. I implemented your solution and it does create the correct pivot table and chart.

Is there any way to re-create this result strictly through the pivot table, though? This solution requires adding a column to the data but I would like to be able to completely replace the worksheet holding the data at any time with new data without needing to manipulate it at all. Ideally, I want to be able to export the data from Quickbooks to Excel, click refresh on my pivot tables and have everything work right away.

 

[jabu]

I was able to make a little progress, but I'm still having trouble. My pivot table now calculates the time since the last payment like I want, but it breaks if there are any missing payments (i.e., if the first payment received from a customer in the given time period is draw #5, or if we haven't received a payment yet, or if a customer never paid draw #3 but did pay #2 and #4, then it breaks)

I changed the values to Sum of Date - Show Values As - Difference From - Memo - Previous. This is the closest I've gotten so far. I know the "broken" numbers are the serial number of the date of the payment, but I don't know how to handle them here.

Here's what my pivot table looks like now:

income and expenses TEST.xlsx
	A	B	C	D	E	F	G
3	Sum of Date	Column Labels
4	Row Labels	Draw #1	Draw #2	Draw #3	Draw #4	Draw #5	Draw #6
5	Customer A		40	63	34	32	7
6	Customer B		42	35	34	33	-44767
7	Customer C		0	0	0	44264	-44264
8	Customer D		59	65	48	35	59
9	Customer E		24	78	35	66	0
10	Customer F		24	40	38	-44743	0
11	Customer G		94	19	41	68	0
12	Customer H		0	0	0	44334	36
13	Customer I		38	24	51	32	40
14	Customer J		40	28	80	68	44
15	Customer K		-44743	0	0	0	0
Sheet1

 

[jorismoerings]

Hi,

I'm not sure if this can be prevented as, at least to my knowledge, the pivot table does not have error handling when calculating with the standard functions.
That's the reason why i choose to add a helper column to the data.
As a tip:

1. If you want to follow your chosen path, make sure you check what happens if a payment is mentioned twice in the data and insert the correct error handling
2. Another option is to look at functionality in PowerQuery / PowerPivot.

 

[jabu]

It's very frustrating to be so close to the result I want and not being able to get it. Out of curiosity and the principle of it, I'm going to keep fiddling and trying to solve this purely through the pivot table. But realistically, I will probably end up implementing jorismoerings' solution with the helper column. I'll accept that solution at that point.

Could there be a way to use a calculated field that removes the broken entries? I haven't been able to find one.

 

[SpillerBD]

Could there be a way to use a calculated field that removes the broken entries? I haven't been able to find one.

Yes, and probably not practically as a better option to PowerQuery/PowerPivot.
Old (and Still) available is the MDX language for calculating and querying. This requires use of the OLAP feature available. These complications and breadth of available references that are current just don't make it a good option. Its also a much steeper learning curve for all the pieces you need to pull together.

A lot of people prototype their PQ solutions on the worksheet and then use PQ to simplify the process since it automates your tasks.

 

[avicric]

@jabu
Try this....
Add a new pivot table from your raw data and when adding the pivot table click the box at the bottom which will add the data to a data model.....then click ok to create the pivot table...

Once the pivot table on a new sheet....click inside of it and then goto the data tab on the file menu and click on Manage data.....this will open the power pivot window in excel.....here you can create the required calculate columns or measure to get your desired results....

Please note :: the new columns created will not be added to your main data source but will be available for use inside the pivot table list window in the excel sheet....

Hope this helps

 

[jabu]

I've been lacking time to work on this much, so I ended up using a helper column in the source data. I looked into PowerPivot/PowerQuery but I didn't have it in me to go and learn a bunch of new functions/syntax for this.

The helper column ended up being a bit more complicated than originally planned because my actual source data is more complex than the example data I posted. I modified jorismoerings' formula like so:

=IFERROR(DATEDIF(LOOKUP(2,1/(($A$1:$A1=A2)*($E$1:$E1=E2)*($H$1:$H1=H2)),$B$1:$B1),B2,"d"),0)

Column A is transaction type, column E is customer, column H is transaction account, and column B is date. So this function finds the most recent transaction that has the same type, customer, and account as the current transaction, and then returns the days elapsed since then. This allows me to compare days between all kinds of different things, not just draws.

Thanks everyone for your help.