3 Replies Latest reply on Nov 17, 2016 8:00 AM by David Li

    "cross" filtered data

    sagiv malki

      Hi,

       

      I have few customers' groups - business, premium and private, and a list of code transactions (e.g., 101,103,206,209 etc..)

       

      I'm trying to display the amount of transaction for each group in one sheet.

      The problem is that I each customer group has it's relevant transactions. For example, transactions codes to premium customers are 101 and 103, and for private customers 101 and 206. 

       

      So I want that total transactions for all groups and sub-totals (for example, when I'm choosing to display only premium customers using filter) will take into account only the relevant transactions.

       

      How can I do it?

       

      Your help will be appreciated.

        • 1. Re: "cross" filtered data
          David Li

          Hi Sagiv, you should be able to do this using a parameter. You probably won't be able to do everything you described using just normal filters, since the transaction codes appear to overlap between the categories you've specified.

           

          1. Create a string parameter that gives you options from a list. That list should consist of the customer groups. Let's call this parameter [Customer Group].
          2. Create a calculated field that you'll use as a filter. It'll probably look something like this:

            CASE [Customer Group]

                 WHEN "Premium" THEN [Code] = 101 OR [Code] = 103

                 WHEN "Private" THEN [Code] = 101 OR [Code] = 206

            ... (Add your other groups here)

            END

          3. Add this as a filter and include TRUE.
          • 2. Re: "cross" filtered data
            sagiv malki

            Hi,

            First of all I'd like to thank you for your answer.

            The problem is it tells me that I can't use boolean type in case expression.

             

            any suggestions?

            • 3. Re: "cross" filtered data
              David Li

              Ah, I totally forgot about that, sorry. You can change this to a standard Boolean statement:

              ([Customer Group] = "Premium" AND ([Code] = 101 OR [Code] = 103))

              OR

              ([Customer Group] = "Private" AND ([Code] = 101 OR [Code] = 206))

              OR

              ... (etc etc)