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

# IF missing both or either denominator or numerator then Null

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

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

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

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

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

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