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?
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.
@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 |
1 Like
@rnunley…your formulas are much fancier than mine…but thank you for the reinforcement that I’m at least pulling from the right place 
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)
2 Likes
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.