Hello,
One of the most useful functions in SQL for a lot of my reporting is SUM used with CASE, which allows me to count or sum rows with very specific combinations of attributes. As a random example, SUM(CASE WHEN pkg_no = 0 and is_sub = 'yes' then 1 else 0 end) would count the number of non-package tickets purchased by subscribers.
I see that Analytics has a CASE function but it seems to work differently and it can't be used within SUM. The syntax example implies that CASE can only be used after a field has already been aggregated, such as "CASE WHEN SUM(paid_amt) > 0 then 1 else 0 end".
The work around I've found involves adding filters to the Value series in a widget. So instead of having several CASE booleans I can just keep adding filters to the formula. This is fine, but I don't love having filters buried in a formula. Has anyone else had success using CASE in Analytics formulae? Even just an example of how you've used CASE would be much appreciated.
Thank you!
Ian
Hi Ian,
That's correct that CASE and IF() cannot be invoked against row-level data prior to some form of aggregation. Filtered Values are the intended technique for that. It is possible to have a conditional within a SUM(), however, we still need to respect that the conditional must be evaluating an aggregate and not attempt to evaluate raw dimensional members.
So CASE WHEN [Production Season] = 'Mendelssohn Mania' THEN doesn't work.
CASE WHEN ( [Total Ticket Count] , [Production Season = Mendelssohn Mania] ) > 0 THEN does work.
SUM ( CASE WHEN ( [Total Ticket Count] , [Production Season = Mendelssohn Mania] ) > 0 THEN 1 ELSE 0 END ) also works. That's kind of a silly example, but look at these Bucketing Amounts examples.
Here's one recent post related to CASE:(+) CASE WHEN function for adding value into column - Reporting & Analytics - Forums - Tessitura Network
Another example you might consider is the pre-built Cancellation Impact Dashboard. This is using IF(), but the techniques are the same. There are several formulas in that dashboard that have to attribute On Accounts funds from the selected On Account dashboard filter's payment method(s) as credit card refunds, and when one or more dashboard filter Funds is selected (but not when all are selected), include those funds as retained ticket revenue via contribution. The % Ticket Refunds Retained value is an example of this.
Best,Chris
Wow, thanks for all this. Very helpful!