3 Replies Latest reply on Sep 26, 2016 3:52 AM by Sreekanth Kasaraneni


    Govind Ramchetty


      I have query like

      (sum(IF [Inv Source Nm]<>'GMP' then [Var_Total_Expense] else 0 end)


      In my Inv Source Nm getting null values also.But above condition not taking NULL when i give Inv Source Nm<>'GMP'


      How can i give my syntex.I have to include <> in NUll values also.



      Govind R

        • 1. Re: ISNULL
          Sreekanth Kasaraneni

          Hi Govind,


          Instead of using IF conditional statement, Try to use IIF statement.


          i have done using superstore data set.



          SUM(IIF([Sub-Category] <> "Accessories", [Sales],0 ))


          so its displaying sub-category item Accessories sales as 0 and rest of the items their values.


          • 2. Re: ISNULL
            Govind Ramchetty

            I given Like below


            (sum(IIF([Inv Source Nm]<>'GMP',[Var_Total_Expense],0))/sum([Var_Total_Expense]))*100


            Its not picking..Can you help

            • 3. Re: ISNULL
              Sreekanth Kasaraneni

              Hi Govind,


              I understand now, you want the Null values of [Inv Source Nm] field should be shown. please follow below procedure


              written two calculations


              Calculation2 (Replace [Sub-Category] with [Inv Source Nm])

              IF ISNULL([Sub-Category]) THEN "Null" ELSE [Sub-Category] END


              Calculation1 (Replace [Sales] with [Var_Total_Expense])

              SUM(IF [Calculation2] <> "Art" THEN [Sales] ELSE 0 END)


              Now drop Calculation2 to Rows shelf and Calculation1 to Text on Marks shelf and the view will be as shown below


              go to format option of calculation1


              go to pane of format and select as shown below


              now you will get exact values (Check the data in the attached workbook sheets, Your_Req and For_Testing sheets respectively)