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.
Awesome, thanks! There's my term of art ("bucketing") and I'd never have thought of using Default Sales Start date, as we rarely use or pay attention to that. Also, we have four different onsales, typically, but for my purposes I think I can safely use the field for the one I'm interested in.
I was assuming I would do this with Columns, but it appears you cannot put formulae in Columns. I assume that when building them out as values instead, I must create one formula value per "column"?
That's correct Gawain. And you can bucket into single values like 1, 2, and 3. Or you can mix in ranges as well, like 1, 2-3, 4-6...
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).