Ignore values if present in previous row

[robin_heijt]

Hi,

I am running into the following issue:

As you can see my data set contains 5 lines. Each line will have the latest filled date, to indicate that the Req. identifier is filled. But only the person that does actually fill the requisition has a new offer accepted date.

In QS I now want to calculate the "time to fill" with the calculation RangeMin([Latest Filled Date],[New Offer Accepted Date]))-[Req. Creation Date]

The issue now is that it sees the blank lines as a separate value.

I only want the "time to fill" to be calculated for the first instance in this scenario. 

So the idea is: (But this string does not do the trick). Anyone that can help me?

IF(previous("Req. Identifier")="Req. Identifier" and len([Latest Filled Date])>0 and 
len([New Offer Accepted Date])>0, null(), 
RangeMin([Latest Filled Date],[New Offer Accepted Date]))-[Req. Creation Date]) 
as "Time to fill"

[PrashantSangle]

try below

if(len([New Offer Accepted Date])>0,RangeMin([Latest Filled Date],[New Offer Accepted Date])-[Req. Creation Date],0)

Regards,
Prashant Sangle

[robin_heijt]

Hi Prashant,

Thanks for your reply. It seems to work indeed, great!

However the next issue now is that in some cases a Requisition will not always have a "New Offer Accepted Date". Sometimes it will only have the "latest filled date". Hence my logic was to see if the Req ID already has a New offer accepted date present or not. Would you be able to give me an adaptation of your code with your knowledge here?

[PrashantSangle]

can you provide with sample data and expected result.

Regards,
Prashant Sangle

[robin_heijt]

So I changed your string to this:

if(len([New Offer Accepted Date])>0,RangeMin([Latest Filled Date],[New Offer Accepted Date])-[Req. Creation Date],null())

So now it results in null() instead of '0'

As you can see in the below example, this string worked on the requisition ID:  18018360

But now I have another Requisition ID: 18009350

This requisition does not have a new offer accepted date at all.

In this scenario the time to fill should still calculate the time to fill based on:

RangeMin([Latest Filled Date],[New Offer Accepted Date])-[Req. Creation Date]

Thanks