5 Replies Latest reply on May 3, 2018 8:01 AM by Simon Runc

    Accessing Set Values

    Jan Jenko



      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.


      Any ideas?


      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?

        • 1. Re: Accessing Set Values
          Zhouyi Zhang

          Hi, Jan


          Is this what you are after? if yes, please find workbook attached.



          • 2. Re: Accessing Set Values
            Simon Runc

            hi Jan,


            This might help...although I am assuming your real-world problem reflects your example....


            So what I've done is used the SPLIT function to split out the 'raw ingredient' (eg. Strawberry) from the 'final product'...this way I can get Strawberry into the set (whether it's the fruit, or the ice-cream), and the ''Final product' let's me assess if it's a final product, or raw ingredient (eg. If this field ISNULL then it's a raw ingredient). Hopefully from here you can construct the logic you need, but please let me know if not.

            1 of 1 people found this helpful
            • 3. Re: Accessing Set Values
              Jan Jenko

              Zhouyi Zhang


              Not quite... I would still want to have revenue from row ('B', 'banana', 19) because the product is banana and A also sells banana. And I would not want to have revenue from row ('B', 'chocolate ice-cream', 16) because A does not sell chocolate.


              Maybe I should have been clearer when defining what I want: - revenue from any product that is also sold by A (hence I want ('B', 'banana', 19))

                                                                                                                 - revenue from any product that looks like [product1] + ' ice-cream' where  [product1] is sold by A


              Thanks though!

              • 4. Re: Accessing Set Values
                Jan Jenko

                Hi Simon,


                This worked, thanks!
                I'm almost disappointed I didn't come up with this on my own... But I'm really glad and impressed at how well this forum functions


                Attached is what I was looking for, using your solution.

                • 5. Re: Accessing Set Values
                  Simon Runc

                  No problem...I've been too close to a problem to see the "wood from the trees" many, many times! and the great folks on the community have come to the rescue! The community is great, and is such an amazing resource for help and learning.