Days between calculation not working

[kac_zrg]

I have three columns with the following data expressions, which provides me with the dates needed:

a.  if ([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[oldValue] = 'Research') then
([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[dateAdded]) else (NULL)

b.  if ([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[oldValue] = 'Slate Development') then
([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[dateAdded]) else (NULL)

c.  if ([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[oldValue] = 'Slate Presented') then
([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[dateAdded]) else (NULL)

When I try to calculate days between b-a or c-b, the response is blank. I am assuming the (null) may be impacting this, but not sure.

Further, is there a way to calculate c-b but if b is blank, then c-a?

[Lynn]

I have three columns with the following data expressions, which provides me with the dates needed:

a.  if ([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[oldValue] = 'Research') then
([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[dateAdded]) else (NULL)

b.  if ([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[oldValue] = 'Slate Development') then
([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[dateAdded]) else (NULL)

c.  if ([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[oldValue] = 'Slate Presented') then
([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[dateAdded]) else (NULL)

When I try to calculate days between b-a or c-b, the response is blank. I am assuming the (null) may be impacting this, but not sure.

Further, is there a way to calculate c-b but if b is blank, then c-a?

Any calculation involving a null value will result in null.

You can use "is null" to determine whether to compute c-b or c-a. There is also a "coalesce" statement that takes the first non-null value. In either case you'd likely want to deal with a situation where both a and b are null.

[kac_zrg]

Thanks Lynn -

I rewrote the expressions to:

case when ([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[oldValue] = 'Slate Development') then
([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[dateAdded]) end

I am still pulling the correct dates, however still no calculations of date between. I am a novice, and at a loss.

[Lynn]

Thanks Lynn -

I rewrote the expressions to:

case when ([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[oldValue] = 'Slate Development') then
([PRESENTATION VIEW - JOB ANALYSIS].[JOB EDIT HISTORY].[dateAdded]) end

I am still pulling the correct dates, however still no calculations of date between. I am a novice, and at a loss.

Based on your original post you are only ever going to have one of the three dates populated for each row returned because all three expressions look at the same data item called oldValue. It can only ever be one value so you will only ever get one of the three expressions returning a date. There is, therefore, no way to compute a number of days between those separate expressions.

[kac_zrg]

Thank you - that is what I suspected. Appreciate the response.