I am attempting to write a formula in analytics to discern whether the value of the "Constituent Display Name" is equal to the value of the "Recipient Constituent Display Name" for a set of orders (basically wanting to find out which tickets/orders have been shared to other patrons). I figured a CASE statement might be a good route to go for this, but I am having troubles finding a formula that works. Here's what I have written so far:
CASE WHEN [Constituent Display Name] = [Recipient Constituent Display Name] THEN 'Equal' ELSE 'Not equal' END
The message I am getting with this formula is "Function Syntax Error: End expected." I also tried swapping out "equal" and "not equal" for 1 and 0, and that gave me the error message "Error in "CASE" statement in 'condition_1': Expecting parameter type of 'Boolean' that consists of expression types 'Member Expression'. Instead found 'Dimension Expression'".
If anybody has any tips, that would be appreciated! Thanks!
In general, the first CASE statement is ended correctly. Is there possibly another CASE statement in the expression that is missing the END keyword? If all CASE statements in the expression are not properly ended, then this message would be displayed.
For the second error message, Ensure the data types of the columns in the statement are of the same type. If they are, there may be something else in the larger expression causing the error.
Hi Michael,
Conditional statements support only the comparison of aggregated values and not raw data nor string values like the display names of constituents. Also, the results cannot be displayed as a string value like "Equal" and must be a numeric raw or calculated value. For example, you might try...
CASE WHEN MAX( [Constituent ID] ) = MAX( [Recipient Constituent ID] ) THEN 1 ELSE 0 END
However, this would need to be calculated against each distinct sub-lineitem. So either requires [SLI ID] on rows and each will display either a 1 or 0, or use Multi-Pass Aggregation to group that formula by [SLI ID] to an overall count of recipients who are also the owner.
All that said, and perhaps having buried the lead, there's a Seats and Tickets Cube field in the SEAT & TICKET DETAILS table called [Recipient Specified Flag].
Y is returned if a recipient has been selected for an SLI. N is returned if a recipient has not been selected.
It may work more easily to use this on Rows of your widget instead of a formula.
Chris
This is exactly what I needed, thank you so much!