I'd like to be able to do two things, and I don't think either is possible.
The first is most likely: I'd like to be able to break out values (i.e. add a column) that was "tickets per order", so all orders with one ticket would go in the first column, all orders with two tickets would go in the second column, etc.
The second is highly unlikely, but strikes me as a very desirable thing, which is the ability to compare daily sales between two seasons from an arbitrary set of dates: the date of the onsale. Our onsale dates are always specific days of the week, and float around based on various other circumstances, so comparing by "day of the year" isn't helpful.
Anyone else wanted to plot these or have ideas about them?
Hi Gawain,
For the first, definitely. Check out bucketing starting at 11:42 in this webinar. You'll bucket with a formula pattern along these lines:
SUM ( [Order ID] , IF ( [Total Ticket Count] = 1 , 1 , NULL ))
For the second, there does exist fields for time between onsale and order date. The performance onsale date is the Default Sales Dates Start Date in the General tab of the Performance setup. If you can align those for the performances within a season, then you can see accurate, relative time between season onsale and order date, over multiple seasons.
So, I don't know if this is a problem, but ORDER ID needs to be a count, not a sum. I've been trying this formula:
count([Order ID], IF( [Total Ticket Count] = 2, 1, NULL ))
But that just give 1 or 0.
NM: I do want a sum there because I'm summing the result of the IF, right? It's not a filter returning Order ID?
Re: "NM" that's correct. It'll SUM all the resulting 1s and NULLs.
Okay, upping the ante:
SUM( [Order ID] , IF( COUNT( [Performance ID]) >= 5 AND COUNT( [Performance ID]) < 15 , 1, NULL) )
When I use [Performance ID] here (and [Order Id] in other examples), is there an implicit "distinct" operation?
Yes. Using COUNT ( [Performance ID] ) explicitly means distinct count (as opposed to a DUPCOUNT which is not a distinct count, but rather a row count).