1 Reply Latest reply on Jun 28, 2017 2:21 PM by Okechukwu Ossai

    Action Filter Help

    Eric Balash

      Hello All,

       

      I am trying to set up an action filter for the data that I have. In my data, 1 Person can have 0, 1, or many Cereals. The way the dashboard is set up, it has the cereals as rows, with a bar chart showing the total aggregate cost for each cereal. When a cereal is selected from the filter, I want to filter the bar chart results for all individuals that have that cereal in their basket, AND to also include the other cereals that they may have.

       

      Scenario:                      

      JohnMaryMike
      Apple Jacks  ($4)Cheerios ($3) Apple Jacks ($4)
      Fruit Loops   ($5)  Cookie Crisp ($7) Cheerios ($3)
      Cheerios       ($3) Trix ($2)

       

      When nothing is selected, this is what should show in the bar chart:

      Cheerios: $9

      Apple Jacks: $8

      Cookie Crisp: $7

      Fruit Loops: $5

      Trix: $2

       

      If "Apple Jacks" is selected, this is what should show in the bar chart:

      Apple Jacks: $8

      Cheerios: $6

      Fruit Loops: $5

      Trix: $2

       

      Thanks,

      Eric

        • 1. Re: Action Filter Help
          Okechukwu Ossai

          Hi Eric,

           

          There could be some other ways to do this using sets but my approach involves parameter, LOD and calculated field.

           

          Step 1: Create a parameter [Cereal Selector]

          This will be a string list of all cereals in your database. The first selection in the list ensures all items are displayed by default until a cereal is selected.

           

          Step 2: Create calculated field [Customer Cohort]

          [Customer] =

          (IF [Cereal Selector] = "--Select a Cereal--" THEN [Customer]

          ELSE

              IF [Cereal Selector] = [Cereals] THEN [Customer]

              END

          END)

          This is a Boolean expression and will return "True" if a customer bought the selected cereal. However, this will be True for the selected cereal only.

          "--Select a Cereal--" is the default value so will return "True" for all customer-cereal combinations.

           

          Step 3: Create a calculated field [Bought Together]

          {FIXED [Customer]: MAX([Customer Cohort])}

          Using  this LOD expression, the result in Step 2 is forced to return "True" for all the cereals bought by customers already validated by the Boolean expression.

          See Sheet 3 in the attached workbook to see what Steps 2 and 3 are doing.

           

          Step 4. Build your views. Put [Bought Together] on the filter shelf and set to "True"

           

          See attached workbook in version 9.3.

          Hope this helps.

          Ossai