I'm trying to use Sets to create a calculated field, but it seems what I'm trying to achieve might not be supported. I'm aware of the IF [Set1] THEN 'whatever' syntax, but that doesn't help here. What would be perfect is (IF [field] IN [Set] THEN ...) syntax or some equivalent.
Here is some data:
(company, product, revenue)
(A, strawberry, 10)
(A, banana, 21)
(B, chocolate ice-cream, 16)
(B, strawberry ice-cream, 16)
What I want is the sum of revenue generated by products also sold by A or by ice-creams made of products sold by A. So I want rows 1, 2 and 4, a set containing them (or their [product]).
I tried two approaches:
1. Create [Set1] of [product] where [company] = 'A' ([Set1] = ['strawberry', 'banana']). Then create calculated field [cf] = (IF [company] = 'A' THEN [product] + ' ice-cream' END) and a [Set2] based on it ([Set2] = ['strawberry ice-cream', 'banana ice-cream']). Then I'd like to create their union but that isn't supported because [Set1] is based on [product] and [Set2] is based on [cf]. So out of luck there.
2. In the second approach I likewise create [Set1] in the same way. Then I'd like to create a calculated field that would look something like this: [cf] = IF ([Set1] OR (ENDSWITH([product], 'ice-cream') AND REPLACE([product], ' ice-cream', '') IN [Set1])) THEN [revenue] ELSE 0. Summing over [cf] would give me what I want, but REPLACE([product], ' ice-cream', '') IN [Set1] is gibberish.
I attached a sample workbook. Not that it does anything, it's just a ready-made testing ground if anyone is willing to give it a try.
The 'IF [Set1] THEN ...' syntax is too rigid for my needs. If [Set1] is a set of elements from [column], then what 'IF [Set1] THEN ...' does, is for each row it looks at [column] and evaluates '[column] IN [Set1]' if that were valid syntax. But what I want evaluated is '([column] + "blabla") IN [Set1]' or '([column] / 100) IN [Set1]' or '[some_other_column] IN [Set1]'. Is anything like that possible? Any workarounds?
Book1.twbx 5.7 KB