5 Replies Latest reply on Aug 24, 2018 1:00 PM by Okechukwu Ossai

    IF missing both or either denominator or numerator then Null

    Jane Wu

      Hi

       

      I want to know how would one apply this excel formula in tableau.

       

      IF(ISERROR(([Numerator]/[Denominator]) - 1), " ",((Numerator]/[Denominator]) - 1)

       

      must have both denominator and numerator to calculation formula otherwise stay null.

       

      Thanks,

        • 1. Re: IF missing both or either denominator or numerator then Null
          Jim Dehner

          have you tried

           

           

          if isnull(attr(numerator)) Or isnull(attr(denominator)) then Null

          else sum(numerator)/sum(denominator)

          end

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: IF missing both or either denominator or numerator then Null
            Naveen B

            HI Jane,

             

            you can replicate the above calculation like below

             

            IIF(ISNULL([Numerator]/[Denominator]) - 1),NULL,((Numerator]/[Denominator]) - 1))

             

            NULL will give blank value else it will give the N/D-1 Value

             

            Or another way would be

             

            ZN(,((Numerator]/[Denominator]) - 1))

            Will give 0 whenevr ther #Div/0 error

             

            Hope this helps kindly mark this answer as correct or helpful so that it will help others

             

            BR,

            NB

            • 3. Re: IF missing both or either denominator or numerator then Null
              Okechukwu Ossai

              HI Jane,

               

              This should do it. There are 2 options; a non-aggregated and pre-aggregated versions. Either will work depending on how your data is structured.

               

              Non-aggregated version

              IF NOT ISNULL([Numerator]) THEN

                  IF NOT ISNULL([Denominator]) THEN ([Numerator]/[Denominator])-1

                  END

              END

               

               

              Pre-aggregated version

              IF NOT ISNULL(SUM([Numerator])) THEN

                  IF NOT ISNULL(SUM([Denominator])) THEN (SUM([Numerator])/SUM([Denominator]))-1

                  END

              END

               

              Hope this helps.

              Ossai

              • 4. Re: IF missing both or either denominator or numerator then Null
                Jane Wu

                Hi Ossai,

                 

                I can't seem to get it to work. My calculations are so off.

                 

                MTM % field:

                IF

                DATEDIFF('month',DATETRUNC('month',[Work Date]),

                {MAX(DATETRUNC('month',[Work Date]))})=0 AND [Revenue Type]='Fee'

                THEN ZN(({FIXED [Customer Number]:SUM(([Current Mth Actual Revenue]))}/{FIXED [Customer Number]:SUM([Prev Mth Actual Revenue])})-1)

                END

                 

                There are percentage but incorrect answer.

                 

                MTM % Copy Field:

                 

                IF

                DATEDIFF('month',DATETRUNC('month',[Work Date]),

                {MAX(DATETRUNC('month',[Work Date]))})=0 AND [Revenue Type]='Fee'

                THEN (ZN([Current Mth Actual Revenue])/ZN([Prev Mth Actual Revenue]))-1

                END

                • 5. Re: IF missing both or either denominator or numerator then Null
                  Okechukwu Ossai

                  Hi Jane,

                   

                  Are you expecting -174.12% for the example shown? See below suggested solution broken into smaller steps for better understanding and easier error tracking. I don't know all your fields, so feel free to adjust the formula.

                   

                  Step 1: Create calculated field [Current Month]

                  {FIXED [Customer Number], YEAR([Work Date]): MAX(DATETRUNC('month', [Work Date]))}

                   

                  Step 2: Create calculated field [Previous Month]

                  {FIXED [Customer Number], YEAR([Work Date]): MAX(

                  IF DATEDIFF('month', DATETRUNC('month', [Work Date]), [Current Month]) = 1 THEN DATETRUNC('month', [Work Date]) END)}

                   

                  Step 3: Create calculated field [Current Month Revenue]

                  {FIXED [Customer Number], YEAR([Work Date]): SUM( IF [Revenue Type] = 'Fee' AND DATETRUNC('month', [Work Date]) = [Current Month] THEN [Fee Amount] END)}

                   

                  Step 4: Create calculated field [Previous Month Revenue]

                  {FIXED [Customer Number], YEAR([Work Date]): SUM( IF [Revenue Type] = 'Fee' AND DATETRUNC('month', [Work Date]) = [Previous Month] THEN [Fee Amount] END)}

                   

                  Step 5: Create calculated field [MTM %]

                  (([Current Month Revenue] - [Previous Month Revenue])/[Previous Month Revenue]) - 1

                   

                  Hope this helps.

                  Ossai