2 Replies Latest reply on Dec 14, 2012 8:46 AM by Bob Lambert

    Show this or show that or show all

    Bob Lambert

      For superstore sales data, drag Product Name to rows and Sales to columns for a bar chart of sales by product.  I wrote a calculated field, name PosNeg, that is this:

            if sum([Profit])>0 then "Profit"

            elseif sum([Profit])=0 then "No Profit"

            elseif sum([Profit])<0 then "Profit Loss"

      end. 

       

      I can use that to create a parameter to show only products that have a profit or loss or zero profit.  But How do I create it to have the option to show all?  The mutual exclusivity of the calculated field seems to prevent that.

        • 1. Re: Show this or show that or show all
          Dean Hewitt

          Hello Bob,

           

          You need to create a parameter to allow the user to select profit, breakeven, loss or ALL and a calculated field used to filter the data.  The calculated item is as follows:

          CASE [Profit?]

              WHEN 'Profit' THEN IF SUM([Profit]) > 0 THEN 1 ELSE 0 END

              WHEN 'Loss' THEN IF SUM([Profit]) < 0 THEN 1 ELSE 0 END

              WHEN 'Breakeven' THEN IF SUM([Profit]) = 0 THEN 1 ELSE 0 END

              WHEN 'ALL' THEN 1

          END

          This is placed on the filter shelf and set filter value to 1.  The parameter [Profit?] has the four values shown in this calculated item. You also need to have profit on the worksheet.  I have attached an example. 

           

          Note: you can use this method to filter on multiple field at once.  Ue the CASE statement to select teh parameter and the IF logic to perform the required filtering. 

           

          Regards,

          Dean