4 Replies Latest reply on Nov 25, 2016 6:19 AM by Clare Gibson

    Parameter to select different sets of filter options

    Clare Gibson

      This is my first post to the forum, and I wasn't sure exactly how to phrase my question.

       

      I have a dataset of contacts that are all at various stages in a process. There are 4 stages and the contacts work through them sequentially (Stage 1 then Stage 2 then Stage 3 then Stage 4). Some contacts will only proceed as far as Stage 1, others will get to Stage 2 or beyond (there could be dropout at any of the stages). I have a field which captures the furthest stage that each contact has reached, which looks a little like this:

       

      ID
      Stage
      1

      Stage 1

      2Stage 2
      3Stage 2
      4Stage 3
      5Stage 4

       

      I'd like for my users to be able to filter based on Stage, such that by filtering on 'Stage 1' the results would show for every record who had reached or passed Stage 1, filtering on 'Stage 2' would show results for every record that had reached or passed 'Stage 2', etc.

       

      In reality this means that if a user picks 'Stage 1' as the filter option, they should see all of the records in the dataset (since all records reach Stage 1).

      If they select 'Stage 2' then they should see any record tagged Stage 2, Stage 3, or Stage 4.

      If they select 'Stage 3' then they should see any record tagged Stage 3 or Stage 4

      If they select 'Stage 4' then they should see any record tagged Stage 4

       

      Can anyone advise me on the best way to achieve this?

       

      Many thanks in advance.

       

      Unfortunately my data is proprietary so I cannot share workbooks.

        • 1. Re: Parameter to select different sets of filter options
          kumar.c

          Hi Clare,

           

          Could you please share the workbook with some example similar to your scenario based.

           

          Thanks

          • 2. Re: Parameter to select different sets of filter options
            Simon Runc

            hi Clare,

             

            Yes I think I understand what you need...let me know if I've misunderstood (...certainly wouldn't be the first time!)

             

            So what I've done is first create an INTEGER version of each stage (you probably don't need this as we can MAX strings and in that "Stage 2" is higher than "Stage 1"...alphabetically, but to be sure I created it as a number)

             

            [Stage INT]

            INT(RIGHT([Stage],1))

             

            I then used a LoD to bring back the MAX Stage for each ID

            [Max Stage by ID]

            {FIXED ID: MAX([Stage INT])}

             

            I then created a parameter with the values 1 to 4 (and used Alias to make them more "human readable"

             

            and then the final piece of the puzzle was to create a filter based on the field, and the parameter

            [Filter to Selected]

            [Max Stage by ID]>=[Select Stage]

             

            which I brought onto the filter shelf, and set to true.

             

            Hopefully this is what you were after, and makes sense, but let me know if not (on either count!)

            2 of 2 people found this helpful
            • 3. Re: Parameter to select different sets of filter options
              Clare Gibson

              Hi Simon,

               

              This was hugely helpful, thank you. And such a quick response too.

               

              I really like the solution of converting the 4 stages in to integers, which allows for the >= formula in the filter. You have no idea how many IF statements I was trying and getting totally lost in the logic each time!

               

              Thanks again.

              • 4. Re: Parameter to select different sets of filter options
                Jian Wang

                You can create a simple data set with sample below.  Join this data set with your data by Stage_To_Max  = [your data set].Stage.  Use Stage_Included field as filter.

                 

                Stage_To_MaxStage_Included
                Stage 1Stage 1
                Stage 2Stage 1
                Stage 2Stage 2
                Stage 3Stage 1
                Stage 3Stage 2
                Stage 3Stage 3
                Stage 4Stage 1
                Stage 4Stage 2
                Stage 4Stage 3
                Stage 4Stage 4

                 

                Also, not sure how large your data set is. If there are many rows, I would suggest you to use numeric Stage ID in your data set.    

                Stage_To_Max_IDStage_To_MaxStage_Included
                1Stage 1Stage 1
                2Stage 2Stage 1
                2Stage 2Stage 2
                3Stage 3Stage 1
                3Stage 3Stage 2
                3Stage 3Stage 3
                4Stage 4Stage 1
                4Stage 4Stage 2
                4Stage 4Stage 3
                4Stage 4Stage 4

                   

                 

                So your data set should look like

                 

                ID

                StageID

                1

                1

                2 2
                32
                4 3
                5 4

                 

                And you join Stage ID with Stage_To_Max_ID

                Hope that helps.