3 Replies Latest reply on Sep 20, 2018 9:01 AM by Ronald Tor Das

    Question about multi-select and FIXED

    Ronald Tor Das

      I have a data set that looks like this

       

      CustomerIdQuestionIdQuestionTxtAnswerIdAnswerTxtCalculated Field (product filter)
      11What did the customer buy1SnickersSnickers
      12What did the customer like about the store11LocationSnickers
      12What did the customer like about the store12CleanlinessSnickers
      21What did the customer buy3TimTamsTimTams
      22What did the customer like about the store11LocationTimTams
      31What did the customer buy2MarsTimTams
      31What did the customer buy3TimTamsTimTams
      32What did the customer like about the store12CleanlinessTimTams
      32What did the customer like about the store13PriceTimTams

       

      I want to be able to chart out, at a customer level, their answers to each questions. I also need to be able to filter on customer (ids) based on what they purchased. Previously, I was using this calculated field to compute a product filter :

       

      { FIXED CustomerId : MAX(IF Question = 'What did the customer buy' THEN AnswerTxt ELSE NULL END) }

       

      This allows me to quickly perform filters at a "product bought" level on any further questions in the dataset. Problem with this is that it used to work before we changed the "buy" questions from single select to multi select. In my example above, customerId = 3 bought two products, and because of the "MAX" in the calculation, the option for "Mars" does not appear in the product filter.

       

      Essentially, I'm looking for a way to easily pull out the distinct list of products the customers had purchased - based on the given data structure - and present them in a filter (in multi select fashion) such that I can pick out customers, and therefore the answers, who had purchased these said items. I'm also open to other methods that involves changing the data structure as well.

       

      Link : Tableau Public

      https://public.tableau.com/static/images/Ch/Chocolate_15/Dashboard1/1.png

        • 1. Re: Question about multi-select and FIXED
          David Maning

          Hi Ronald,

           

          I am not quite sure what you are loking for.

           

          So, if you want to determine the amount of products of products bought by customer:

          { FIXED [Customer Id]:COUNTD(

              IF [Question Txt] = 'What did the customer buy'

              THEN [Answer Txt]

              END)}

           

          If you want to have a list of products bought by customers you simply apply:

          IF Question = 'What did the customer buy' THEN AnswerTxt END

           

          Trust this helps.

          D

          • 2. Re: Question about multi-select and FIXED
            Jim Dehner

            good morning

             

            try this

             

             

            it will retrun this

             

             

            or this

             

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Question about multi-select and FIXED
              Ronald Tor Das

              Hi David ,understand that the question is a bit vague, I'm trying to abstract a bigger problem  in a bigger dashboard, but essentially I need to define a list of purchased products based on answers to question 1. Using the list of products, I want to able to filter the subset of customers responses to question 2. In reality I have a dashboard with 10 questions that are driven (filterable) based on their answer to question 1, and could be more complex than a bar chart.

               

              E.g with the right setup, I can ask "what did customers who bought timtams like about the store"

               

              E.g I pick "timtams" from the filter => the backend somehow identifies that customers 2 and 3 bought timtams => such that the question 2 bar chart only shows responses from customers 2 and 3.