7 Replies Latest reply on Sep 27, 2016 7:20 AM by Yunus Gittham

    filter based value swap

    Yunus Gittham

      Hi

       

      I am attaching a TWBX where in I want to swap headers based on a filter selection.

       

      When the selection is "All" then it should display "X" else it should display "Y".

      I am doing two things

      1. Count the number of elements in the DB

      2. Count the number of elements selected in the filter.

      If they match (which means "All" from the filter is selected ) then show a value else show another value.

       

      Can someone help me please?

        • 1. Re: filter based value swap
          Michael Hesser

          Hello Yunus Gittham;

           

          You can count the elements in the DB through a LOD expression:

           

          Total Sale Type Elements

          {fixed:countd([Sale Type])}

           

          And you can count the number of elements selected with the COUNTD function:

           

          Selected Sale Type

          COUNTD([Sales Type])

           

          Using these, you can tell when your user has selected all, because [Total Sale Type Elements] = [Selected Sale Type]

          Otherwise, the user has selected only some (or none).

           

          So all you need is another calculation:

           

          Switcher

          iif([Total Sale Type Elements] = [Selected Sale Type],[The ALL calculation goes here],[The partial calculation goes here])

           

          You may also need a litttle calc to change how the header reads, but I'm confident you can apply the same logic and get to that!

           

          Will this work for you? --Michael

          1 of 1 people found this helpful
          • 2. Re: filter based value swap
            Yunus Gittham

            Hi Michael

            That worked.

             

            Alternately, I also tried,

            "if [number of sales types selected] = 0 then 'strategy' else 'action plan' END"

             

            The 0 would show up when "All" was selected.

            • 3. Re: filter based value swap
              Yunus Gittham

              Hi Michael

               

              Take a look at the attached.

               

              The behavior should be

              1. When All is selected on Product Name the worksheet should show strategy else it should show action.

              2. For anything to show, there should be something selected in the sub-category filter. It cannot be empty.

               

              Its not working.

               

              Basically you have to select "All" on both filters to show "Strategy" which is not the right behavior.

               

              Can you please help?

              • 4. Re: filter based value swap
                Michael Hesser

                Hello Yunus Gittham

                I think this is because you have additional Filters at play.

                 

                I modified one calculation and I think I have the results you need:

                 

                Count of Product in DB

                { fixed [Category],[Sub-Category] : COUNTD([Product Name]) }

                 

                Alternatively, if you think you might be adding more filters in the future, the exclude command may be a more elegant fit:

                 

                Count of Product in DB

                {exclude [Product Name]: countd([Product Name])}

                 

                *Note: I don't work much with excludes, so play around and see if it gives you the results you need

                 

                Previously, your calculation was computing the non-filtered total of all [Product Name]. This made it impossible for the values to ever be equal unless ALL was selected in both cases.

                 

                Please let me know if this works for you

                • 5. Re: filter based value swap
                  Yunus Gittham

                  @Michael Hesser

                   

                  Thank you for helping out. Unfortunately, this did not work.

                   

                  I tried the include and exclude both.

                   

                  The include seemed more viable to me too as I have used it in the past.

                  • 6. Re: filter based value swap
                    Michael Hesser

                    Hello Yunus Gittham;

                     

                    I've attached a TWBX that seems to work for me; I've temporarily mapped the values (total choices vs. total selected) to visually illustrate how the values (and header) changes.

                     

                    This may not be the smoothest transition: I noticed some "hinky" behavior when switching between filters choices.

                     

                    Perhaps someone smarter than myself can assist you with that?

                     

                    Crossing fingers! --Michael

                    1 of 1 people found this helpful
                    • 7. Re: filter based value swap
                      Yunus Gittham

                      Thanks Michael.

                       

                      It works.

                       

                      I have to try it in the database I am working on. But the concept is similar. three alpha numeric filters which decide the value to be displayed.