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 :slight_smile:


#5

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

The subtotal function will be your friend.

Export records for Event 18-T22 Indigo Girls 3382 2019-03-06.xlsx (19.7 KB)


#6

@rnunley This is EXTREMELY helpful! Thank you so much. :+1:t3:


#7

Just to vouch for the responses above, we also use the Sales Tab for quick looks and when I need to answer specific or fiddly questions, I usually do something in Excel with exported ticket data like Randale. There is the “cash flow - ticket inventory analysis” report, too, though, which is a nice overview of sales trends by day, week or month. I sometimes use the “record count” report, too, to build some custom sales timeline things.