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

    ISNULL

    Govind Ramchetty

      Hi

      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.

       

      Thanks

      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.

           

          Calculation1

          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

              1.jpg

              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)