Excel 2013 - Requesting Pivot Table Calculation Solution 1

[beadedbytes]

Please example below taken from part of a pivot table (exception: not included in pivot is column labeled as ‘Returns4’).

Mailing#	Week#	Ct Mailed	 Returns1	Returns2	Returns3	Returns4
Mailing1	Wk01	500,569 	41 		41 		2.43%		0.01%
		Wk02	0 		335 		376 		22.31%		0.08%
		Wk03	0 		381 		757 		44.93%		0.15%
		Wk04	0 		354 		1,111 		65.93%		0.22%
		Wk05	0 		360 		1,471 		87.30%		0.29%
		Wk06	0 		214 		1,685 		100.00%		0.34%
Total	 	 	500,569 	1,685

We want to be able to look at the numbers of returns from monthly mailings in a couple of different views. The export used to build the pivot table is comprised of the 1st four columns shown in the pivot table. For each mailing, 'Mailing#' and Total ‘Ct Mailed’ appear once in the raw data and returns from the mailing are provided by week only. And, we track 13-14 weeks for each mailing. See below for descriptions of Returns1, Returns2 and Returns3.

Returns1	Total number of returns by week

Returns2	Running cumulative total of returns by week

Returns3	Weekly cumulative total (Returns2) divided by 
                total number of returns (Total Returns1).  
                When the returns stop, this gives us a sense 
                of the return pattern for the mailing in question.

I would like to add another view to the pivot table -- ‘Returns4’ (description listed below). However, I have not been able to figure out how to incorporate a formula either by using a calculated field or the Pivot features under ‘Value Field Settings’ (‘Show values as’ coupled with ‘base field’).

Returns4	Its calculation -- number of cumulative returns (‘Returns2’) 
                divided by Total ‘Ct Mailed’.
                This information tells us how well (or not) a mailing performed.

Thank you in advance for feedback and solution(s)!

 

[SkipVought]

Hi,

You don't really need a Pivot Table.

I assume that your source data looks like this...
[pre]
Mailing# Week# Ct Mailed Returns1
Mailing1 Wk01 500,569 41
Mailing1 Wk02 0 335
Mailing1 Wk03 0 381
Mailing1 Wk04 0 354
Mailing1 Wk05 0 360
Mailing1 Wk06 0 214
[/pre]

So then,
Convert your table to a Structured Table. The default table name will be Table1. This makes the maintenance of your data simpler and the formulas easier to understand and maintain.

Assuming that your table's top left cell is A1, these are the formulae for...
[tt]
Returns2: =[@Returns1]+IF([@[Mailing'#]]=A1,E1,0)
Returns3: =[@Returns2]/SUMIF([Mailing'#],[@[Mailing'#]],[Returns1])
Returns4: =[@Returns2]/SUMIF([Mailing'#],[@[Mailing'#]],[Ct Mailed])
[/tt]

...yielding...
[pre]
Mailing# Week# Ct Mailed Returns1 Returns2 Returns3 Returns4
Mailing1 Wk01 500,569 41 41 2.43% 0.01%
Mailing1 Wk02 0 335 376 22.31% 0.08%
Mailing1 Wk03 0 381 757 44.93% 0.15%
Mailing1 Wk04 0 354 1111 65.93% 0.22%
Mailing1 Wk05 0 360 1471 87.30% 0.29%
Mailing1 Wk06 0 214 1685 100.00% 0.34%
Mailing2 Wk01 1000 50 50 100.00% 5.00%
[/pre]

If you prefer not to use Structured Tables, then these are your three formulae...
[tt]
Returns2: =D2+IF(A2=D1,H1,0)
Returns3: =E2/SUMIF(A:A,A2,D)
Returns4: =E2/SUMIF(A:A,A2,C:C)
[/tt]
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[beadedbytes]

Hi, Skip,

Thank you for reminding me the value of using a structured table.

FYI ... Initially, I was not able to get the formulas to work. However, I removed one of the double brackets and ... VOILA! Expected results appeared.

Given this solution, I now need to go and read up on the power of using structured tables (and using them to create charts). I may be in touch with additional questions.

Do you know how much easier you make my work life? Saying simply 'Thank You' doesn't seem to be enough ...

 

[beadedbytes]

Skip,

An addendum. The raw data actually contains the 'Week' field (added below).

After I export the data, I then manually enter information into the 'Week#' field. Is there a formula that would automatically populate 'Week#' field?

Mailing#	Week		Week#	Mailing Ct	Returns1	Returns2	Returns3	Returns4
Mailing1	03/02/15	Wk01	500,569 	41 		41 		2.43%		0.01%
Mailing1	03/09/15	Wk02	      0 	335 		376 		22.31%		0.08%
Mailing1	03/16/15	Wk03	      0 	381 		757 		44.93%		0.15%
Mailing1	03/23/15	Wk04	      0 	354 		1,111 		65.93%		0.22%
Mailing1	03/30/15	Wk05	      0 	360 		1,471 		87.30%		0.29%
Mailing1	04/06/15	Wk06	      0 	214 		1,685 		100.00%		0.34%
Total					500,569 	1,685

 

[SkipVought]

Okay, so far you only show one grouping of mailings. Are there more or always just one?

If more than one grouping, do the dates in each group start with the same date?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[beadedbytes]

Skip,
See attached file. Mailings occur monthly and date range of returns for each mailing can 'overlap'. Let me know if you have other questions.

 

[SkipVought]

You have a sheet with your source data. I named my sheet Source. It has only these columns:

Mailing#
Week
Mailing Ct
Returns1​

Then I have a sheet: Results containing a QUERY. You will need to ADD a query to your sheet ONE TIME. I'll also upload my copy of your Sample workbook. After you add the QueryTable, you'll be able to run this code that will refresh the QueryTable and insert the Week# column into the Results.

Option Explicit

Sub GetResults()
'SkipVought 2/14/2016
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path
    
    sDB = ThisWorkbook.Name
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  a.`Mailing#`"
    sSQL = sSQL & ", a.Week"        '[b]
    sSQL = sSQL & ", 'Wk' & Format((a.Week-"
                sSQL = sSQL & "("
                sSQL = sSQL & "select min(b.Week) "
                sSQL = sSQL & vbLf
                sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`Source$` b "
                sSQL = sSQL & vbLf
                sSQL = sSQL & "where b.`Mailing#`=a.`Mailing#`"
                sSQL = sSQL & ")"
    sSQL = sSQL & ")/7+1,'00') as [Week#]"  '[/b]
    sSQL = sSQL & ", a.`Mailing Ct`"
    sSQL = sSQL & ", a.Returns1"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`Source$` a"
    sSQL = sSQL & vbLf
    
    With Sheets("Results").ListObjects(1).QueryTable
        .Connection = sConn
        .CommandText = sSQL
        .Refresh False
    End With
End Sub

Once you run this macro, you can add the columns for:

Returns2
Returns3
Returns4​

...and insert your formulas. The only things that need to change in the forumlas are 1) the Returns2 needs F1 rather than E1 and Returns4 has Mailing Ct heading.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]