getting a unique # of orders under a certain amount for a season

select distinct order_no from t_order a where a.tot_due_amt < 100

or

select distinct customer_no from t_order a where a.tot_due_amt < 100

If you want to do this in  Analytics, start with a Pivot table so you can verify the results. To count the number of unique Order ID add Order ID to the Values and make sure the formula is COUNT([Order ID]).

On your Filters tab, Filter your Season and then add a Filter by Ticket Paid Amount <= 100 AND not = to 0 (to filter out free orders).

Thanks Brian - I sent your select statement to our IT manager.  She is working on that now. 

Thanks so much Neil!  I will start working on this today.

Hi Neil and Michael,

I think this approach above will filter the results to any orders containing any single paid price layer on any ticket that has between 0 and 100 paid. Thus an order with 2 tickets with 60 paid will be included in the results. I have a couple options for you as you progress from just getting constituent counts to getting constituent identities.

Starting with a dashboard set with the necessary Season Fiscal Year, Season, and/or Order Date range filters...

Filter on Order ID that uses a Ranking type set to return the TOP 100000 (in other words, return everything as long as it meets the criteria in the formula), from a formula that only conditionally returns the Total Ticket Paid Amount for the Order ID if that amount is > 0 and <= 100, otherwise it returns "NULL" or "empty set" for that Order ID, removing it from the results. There are 334 orders out of 789 in my example with a Total Ticket Paid Amount greater than 0 and less than 100. (Probably better as an Indicator widget than Pivot.)

When you get to the point of needing to see who these constituents are...

...In Analytics (List Builder below), set up a Pivot widget with

• on Rows add Order ID
• add Grand Totals from the Order ID options menu
• a Value for Total Ticket Paid Amount
• click the Filter Funnel on Total Ticket Paid Amount, and using a Values type filter set
  • <= 100
  • > 0
  • set to use the AND operator
• click the Widget options menu and assign a Jump To Dashboard of _Constituents
• Apply 

From that, clicking the Grand Total value will open the _Constituents dashboard with the Save to Tessitura List option. This list is not dynamic and captures a moment in time. 

FWIW, we cannot use the same, conditional, Ranking type, Order ID filter from the first example when grouping the data by Order ID, but it's also not necessary in order to get the results.

If a more dynamic list is required, or just as a maybe easier option, as Brian suggests above, popping some SQL code into a List might lead a more desirable result. Start with something like this, filtered to desired Ticket History Seasons or Order Dates.

When I generate I get 57 constituents who have a total spend in those seasons < 100. Click the SHOW QUERY button and in the GROUP BY statement, add ", a1.order_no". 

Then save and generate the list once again, which for me is 85 constituents that, within the selected seasons, have at least one order with a total amount < 100.

In all of these examples, constituents who have an order < 100, and have a separate order > 100 within the same filters, are included in the results.