
1. Re: IF missing both or either denominator or numerator then Null
Jim Dehner Aug 24, 2018 9:26 AM (in response to Jane Wu)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 Aug 24, 2018 9:30 AM (in response to Jane Wu)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/D1 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 Aug 24, 2018 10:08 AM (in response to Jane Wu)HI Jane,
This should do it. There are 2 options; a nonaggregated and preaggregated versions. Either will work depending on how your data is structured.
Nonaggregated version
IF NOT ISNULL([Numerator]) THEN
IF NOT ISNULL([Denominator]) THEN ([Numerator]/[Denominator])1
END
END
Preaggregated 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 Aug 24, 2018 11:12 AM (in response to Okechukwu Ossai)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 Aug 24, 2018 1:00 PM (in response to Jane Wu)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