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.



      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




        • 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]


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



          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.