3 Replies Latest reply on Feb 1, 2013 10:00 AM by Rod West

# Explanation of formula? - Oracle BI Discoverer

Hi

Please can someone explain the following formula:

DECODE(NVL(FTE Days Available SUM,0),0,0,FTE Days Absent SUM/FTE Days Available SUM)

I have looked up the DECODE and NVL functions and I believe I understand them.
However the way that the formula is wrote for the following: NVL(FTE Days Available SUM,0) - I think the values are summed before they are checked if they are null. - Is this correct?

Thank you.
• ###### 1. Re: Explanation of formula? - Oracle BI Discoverer
Hello
DECODE is an IF THEN ELSE function with commas separating the command names.

For example, DECODE(a,b,c,d) means IF a = b THEN c ELSE d

So in your case, DECODE(NVL(FTE Days Available SUM,0),0,0,FTE Days Absent SUM/FTE Days Available SUM) this means:

IF NVL(FTE Days Available SUM,0) = 0 THEN 0 ELSE FTE Days Absent SUM / FTE Days Available SUM

Basically it calculates the percentage of days absent compared to days available. However because we cannot divide by a NULL or zero this is why you have to just use zero whenever FTE Days Available is NULL or zero.

Hope this helps
Michael
• ###### 2. Re: Explanation of formula? - Oracle BI Discoverer
Hi Michael,

Thank you for the information.

What I am still unsure about is the NVL(FTE Days Available SUM,0) part
- does it sum the FTE Days Available values before it considers whether they are null?
• ###### 3. Re: Explanation of formula? - Oracle BI Discoverer
Hi,
does it sum the FTE Days Available values before it considers whether they are null?
Yes it does. But you will only get a null for the sum some if all the values in the sum are null or there are no values to sum.

Rod West