6 Replies Latest reply on Jan 9, 2019 2:58 PM by Joe Oppelt

    Help with calculation


      I am trying to create an exclude values filter something like this below:




      IF  SUM(Sales) < 10000 Then 'Below $10,000'




      The idea is to exclude customers when there sales fall below $10,000 when i select the filter and show all customers when i select  'ALL' but i am seeing an extra value 'NULL' in the drop down list. how do i get rid of this NULL vales from the list?



      Thanks for the help in advance !

        • 1. Re: Help with calculation
          Chris McClellan

          The NULL is because you don't have an ELSE in the IF statement


          You might want to add


          ELSE "$10,000 or above"


          as a new second line (making END on the 3rd line) and then it should work as you expect

          • 2. Re: Help with calculation
            Joe Oppelt

            Are you saying you want to filter using the calc you created?


            The calc you made gets set on all customers.  So customers where sum(sales) < 10,000 will have the value you set, and all the rest will have null because you didn't give an ELSE condition in that calc.

            Modify your calc to add ELSE "QQQQ" for now and you'll see that he NULL will be replaced with QQQQ in the filter.


            So you could say ELSE "At least $10,000", and then your filter will make more sense.


            If I totally missed the mark with this reply, upload a sample workbook so I can see first hand what you have and I'll have a concrete example to play with.

            • 3. Re: Help with calculation

              I am just trying to show list of customers with sales and when i say exclude below $10,000 it removes the customers with sales less than $10000. I just want to show (All) and below $10,000 in the list of values. How do i do that?

              • 4. Re: Help with calculation
                Chris McClellan

                The short answer is that you can't have just 2 options being All and Below 10K.


                The long answer is, you MIGHT be able to float a text box over the "over 10K" radio button so that you can't see it.  It will still be there, but users won't be able to get to it.  This is a "hack" though, I'm assuming you're using a dashboard that is fixed size and you don't want to put anything else where the "over 10K" really is.

                • 5. Re: Help with calculation
                  Deepak Rai

                  Try to Create a parameter, with All and Below 10000 options and use it by creating a calculated field which  is TRUE for All and Below 10000 choice and false for >10000. I agree with Chris, you won't be able to achieve it the way you are thinking.

                  • 6. Re: Help with calculation
                    Joe Oppelt

                    Use a parameter with those two choices, and make a calc based on the param value to bring the right set onto the sheet.