 # Report for sales by week by production?

#1

I’m looking for a way to pull historical data by week by show so that I can build a ticket sales pacing report. I’m not finding that possible in the built-in reports. Has anyone else had success with something similar?

#2

I might be doing it the long way…but I use the Sales Tab on the event page and sort it by day. This gives tickets sold by day. I export that info to excel to calculate pace information.

Maybe someone has come up with a better way–but that’s the way that I’m doing it currently.

#3

@Jeffrey has the right idea.

I don’t know how complicated your date lookup is, but here is an example of what I do.

The excel formula I use to get to a consistent weekly date in excel is =B2+7-WEEKDAY(B2) where B2 is the sales date imported from TM and the end of week is the nearest Saturday. To change the week ending date to Friday, the formula would be =B2+7-WEEKDAY(B2+1)

Then I use excel’s subtotal function based on changes in week.

This Table shows the formulas:

Sales Date Sales Qty Refund Qty Quantity Base Price Discount Refund Amt Sales Amt Net Sales Weekly
43417 38 0 38 2136 0 0 2516 2136 =B2+7-WEEKDAY(B2)
43418 4 0 4 248 0 0 288 248 =B3+7-WEEKDAY(B3)
43419 4 -2 2 124 0 -124 268 124 =B4+7-WEEKDAY(B4)
43420 196 -2 194 10028 0 -144 12112 10028 =B5+7-WEEKDAY(B5)
43420 10 0 10 320 320 0 0 0 =B6+7-WEEKDAY(B6)
43421 33 0 33 1566 0 0 1896 1566 =B7+7-WEEKDAY(B7)
=SUBTOTAL(9,C2:C7) =SUBTOTAL(9,D2:D7) =SUBTOTAL(9,E2:E7) =SUBTOTAL(9,F2:F7) =SUBTOTAL(9,G2:G7) =SUBTOTAL(9,H2:H7) =SUBTOTAL(9,I2:I7) =SUBTOTAL(9,J2:J7) Saturday, November 17, 2018 Total
=SUBTOTAL(9,C9:C16) =SUBTOTAL(9,D9:D16) =SUBTOTAL(9,E9:E16) =SUBTOTAL(9,F9:F16) =SUBTOTAL(9,G9:G16) =SUBTOTAL(9,H9:H16) =SUBTOTAL(9,I9:I16) =SUBTOTAL(9,J9:J16) Saturday, November 24, 2018 Total
=SUBTOTAL(9,C18:C23) =SUBTOTAL(9,D18:D23) =SUBTOTAL(9,E18:E23) =SUBTOTAL(9,F18:F23) =SUBTOTAL(9,G18:G23) =SUBTOTAL(9,H18:H23) =SUBTOTAL(9,I18:I23) =SUBTOTAL(9,J18:J23) Saturday, December 01, 2018 Total
=SUBTOTAL(9,C25:C31) =SUBTOTAL(9,D25:D31) =SUBTOTAL(9,E25:E31) =SUBTOTAL(9,F25:F31) =SUBTOTAL(9,G25:G31) =SUBTOTAL(9,H25:H31) =SUBTOTAL(9,I25:I31) =SUBTOTAL(9,J25:J31) Saturday, December 08, 2018 Total
=SUBTOTAL(9,C33:C38) =SUBTOTAL(9,D33:D38) =SUBTOTAL(9,E33:E38) =SUBTOTAL(9,F33:F38) =SUBTOTAL(9,G33:G38) =SUBTOTAL(9,H33:H38) =SUBTOTAL(9,I33:I38) =SUBTOTAL(9,J33:J38) Saturday, December 15, 2018 Total
=SUBTOTAL(9,C40:C47) =SUBTOTAL(9,D40:D47) =SUBTOTAL(9,E40:E47) =SUBTOTAL(9,F40:F47) =SUBTOTAL(9,G40:G47) =SUBTOTAL(9,H40:H47) =SUBTOTAL(9,I40:I47) =SUBTOTAL(9,J40:J47) Saturday, December 22, 2018 Total
=SUBTOTAL(9,C49:C55) =SUBTOTAL(9,D49:D55) =SUBTOTAL(9,E49:E55) =SUBTOTAL(9,F49:F55) =SUBTOTAL(9,G49:G55) =SUBTOTAL(9,H49:H55) =SUBTOTAL(9,I49:I55) =SUBTOTAL(9,J49:J55) Saturday, December 29, 2018 Total
=SUBTOTAL(9,C57:C62) =SUBTOTAL(9,D57:D62) =SUBTOTAL(9,E57:E62) =SUBTOTAL(9,F57:F62) =SUBTOTAL(9,G57:G62) =SUBTOTAL(9,H57:H62) =SUBTOTAL(9,I57:I62) =SUBTOTAL(9,J57:J62) Saturday, January 05, 2019 Total
=SUBTOTAL(9,C64:C70) =SUBTOTAL(9,D64:D70) =SUBTOTAL(9,E64:E70) =SUBTOTAL(9,F64:F70) =SUBTOTAL(9,G64:G70) =SUBTOTAL(9,H64:H70) =SUBTOTAL(9,I64:I70) =SUBTOTAL(9,J64:J70) Saturday, January 12, 2019 Total
=SUBTOTAL(9,C72:C75) =SUBTOTAL(9,D72:D75) =SUBTOTAL(9,E72:E75) =SUBTOTAL(9,F72:F75) =SUBTOTAL(9,G72:G75) =SUBTOTAL(9,H72:H75) =SUBTOTAL(9,I72:I75) =SUBTOTAL(9,J72:J75) Saturday, January 19, 2019 Total
=SUBTOTAL(9,C77:C80) =SUBTOTAL(9,D77:D80) =SUBTOTAL(9,E77:E80) =SUBTOTAL(9,F77:F80) =SUBTOTAL(9,G77:G80) =SUBTOTAL(9,H77:H80) =SUBTOTAL(9,I77:I80) =SUBTOTAL(9,J77:J80) Saturday, January 26, 2019 Total
=SUBTOTAL(9,C82:C88) =SUBTOTAL(9,D82:D88) =SUBTOTAL(9,E82:E88) =SUBTOTAL(9,F82:F88) =SUBTOTAL(9,G82:G88) =SUBTOTAL(9,H82:H88) =SUBTOTAL(9,I82:I88) =SUBTOTAL(9,J82:J88) Saturday, February 02, 2019 Total
=SUBTOTAL(9,C90:C96) =SUBTOTAL(9,D90:D96) =SUBTOTAL(9,E90:E96) =SUBTOTAL(9,F90:F96) =SUBTOTAL(9,G90:G96) =SUBTOTAL(9,H90:H96) =SUBTOTAL(9,I90:I96) =SUBTOTAL(9,J90:J96) Saturday, February 09, 2019 Total
=SUBTOTAL(9,C98:C103) =SUBTOTAL(9,D98:D103) =SUBTOTAL(9,E98:E103) =SUBTOTAL(9,F98:F103) =SUBTOTAL(9,G98:G103) =SUBTOTAL(9,H98:H103) =SUBTOTAL(9,I98:I103) =SUBTOTAL(9,J98:J103) Saturday, February 16, 2019 Total
=SUBTOTAL(9,C105:C110) =SUBTOTAL(9,D105:D110) =SUBTOTAL(9,E105:E110) =SUBTOTAL(9,F105:F110) =SUBTOTAL(9,G105:G110) =SUBTOTAL(9,H105:H110) =SUBTOTAL(9,I105:I110) =SUBTOTAL(9,J105:J110) Saturday, February 23, 2019 Total
=SUBTOTAL(9,C112:C117) =SUBTOTAL(9,D112:D117) =SUBTOTAL(9,E112:E117) =SUBTOTAL(9,F112:F117) =SUBTOTAL(9,G112:G117) =SUBTOTAL(9,H112:H117) =SUBTOTAL(9,I112:I117) =SUBTOTAL(9,J112:J117) Saturday, March 02, 2019 Total
=SUBTOTAL(9,C119:C122) =SUBTOTAL(9,D119:D122) =SUBTOTAL(9,E119:E122) =SUBTOTAL(9,F119:F122) =SUBTOTAL(9,G119:G122) =SUBTOTAL(9,H119:H122) =SUBTOTAL(9,I119:I122) =SUBTOTAL(9,J119:J122) Saturday, March 09, 2019 Total
=SUBTOTAL(9,C2:C122) =SUBTOTAL(9,D2:D122) =SUBTOTAL(9,E2:E122) =SUBTOTAL(9,F2:F122) =SUBTOTAL(9,G2:G122) =SUBTOTAL(9,H2:H122) =SUBTOTAL(9,I2:I122) =SUBTOTAL(9,J2:J122) Grand Total

This Table shows the output:

Sales Date Sales Qty Refund Qty Quantity Base Price Discount Refund Amt Sales Amt Net Sales Weekly
Tuesday, November 13, 2018 38 0 38 2136 0 0 2516 2136 Saturday, November 17, 2018
Wednesday, November 14, 2018 4 0 4 248 0 0 288 248 Saturday, November 17, 2018
Thursday, November 15, 2018 4 -2 2 124 0 -124 268 124 Saturday, November 17, 2018
Friday, November 16, 2018 196 -2 194 10028 0 -144 12112 10028 Saturday, November 17, 2018
Friday, November 16, 2018 10 0 10 320 320 0 0 0 Saturday, November 17, 2018
Saturday, November 17, 2018 33 0 33 1566 0 0 1896 1566 Saturday, November 17, 2018
285.00 (4.00) 281.00 \$14,422.00 \$320.00 \$(268.00) \$17,080.00 \$14,102.00 Saturday, November 17, 2018 Total
84.00 - 84.00 \$4,048.00 \$330.00 \$- \$4,558.00 \$3,718.00 Saturday, November 24, 2018 Total
42.00 - 42.00 \$2,124.00 \$- \$- \$2,544.00 \$2,124.00 Saturday, December 01, 2018 Total
49.00 - 49.00 \$2,358.00 \$- \$- \$2,848.00 \$2,358.00 Saturday, December 08, 2018 Total
39.00 - 39.00 \$1,718.00 \$- \$- \$2,108.00 \$1,718.00 Saturday, December 15, 2018 Total
49.00 (2.00) 47.00 \$2,014.00 \$84.00 \$(104.00) \$2,484.00 \$1,930.00 Saturday, December 22, 2018 Total
45.00 - 45.00 \$1,890.00 \$- \$- \$2,340.00 \$1,890.00 Saturday, December 29, 2018 Total
33.00 - 33.00 \$1,396.00 \$- \$- \$1,726.00 \$1,396.00 Saturday, January 05, 2019 Total
42.00 - 42.00 \$1,844.00 \$64.00 \$- \$2,180.00 \$1,780.00 Saturday, January 12, 2019 Total
9.00 (2.00) 7.00 \$394.00 \$- \$(104.00) \$568.00 \$394.00 Saturday, January 19, 2019 Total
19.00 - 19.00 \$758.00 \$- \$- \$948.00 \$758.00 Saturday, January 26, 2019 Total
87.00 - 87.00 \$3,334.00 \$2,220.00 \$- \$1,384.00 \$1,114.00 Saturday, February 02, 2019 Total
28.00 - 28.00 \$1,166.00 \$84.00 \$- \$1,342.00 \$1,082.00 Saturday, February 09, 2019 Total
51.00 - 51.00 \$2,122.00 \$84.00 \$- \$2,528.00 \$2,038.00 Saturday, February 16, 2019 Total
54.00 (2.00) 52.00 \$2,124.00 \$- \$(84.00) \$2,728.00 \$2,124.00 Saturday, February 23, 2019 Total
79.00 (2.00) 77.00 \$3,894.00 \$- \$(104.00) \$4,768.00 \$3,894.00 Saturday, March 02, 2019 Total
18.00 - 18.00 \$756.00 \$- \$- \$936.00 \$756.00 Saturday, March 09, 2019 Total
1,013.00 (12.00) 1,001.00 \$46,362.00 \$3,186.00 \$(664.00) \$53,070.00 \$43,176.00 Grand Total

#4

@rnunley…your formulas are much fancier than mine…but thank you for the reinforcement that I’m at least pulling from the right place #5

Here is the actual file I worked from. Feel free to play with it.

The subtotal function will be your friend.

#6

@rnunley This is EXTREMELY helpful! Thank you so much. 