Calculated Column: Return 1st Date where Value in Another Column is Greater Than X

[Kirsten Gustafson 2]

Hi,

I was hoping to get some help with the correct equation for a calculated column.

My current dataset looks something like this:

Date EntityName Dataset Value

1/1/2018 Apple #1 0

2/1/2018 Apple #1 0

3/1/2018 Apple #1 50

4/1/2018 Apple #1 100

5/1/2018 Apple #1 0

6/1/2018 Apple #1 50

7/1/2018 Apple #1 200

8/1/2018 Apple #1 150

1/1/2018 Apple #2 100

2/1/2018 Apple #2 200

3/1/2018 Apple #2 75

4/1/2018 Apple #2 100

5/1/2018 Apple #2 0

6/1/2018 Apple #2 50

7/1/2018 Apple #2 200

8/1/2018 Apple #2 150

1/1/2018 Pear #2 3

2/1/2018 Pear #2 200

3/1/2018 Pear #2 75

I would like a new calculated column that can tell me what the Date was where the first Value exceeded a threshold (for example 10), for each Entity/Dataset group.

For example, it would look like this for the above dataset:

Date EntityName Dataset Value FirstDate>10

1/1/2018 Apple #1 0 3/1/2018

2/1/2018 Apple #1 0 3/1/2018

3/1/2018 Apple #1 50 3/1/2018 * This date is the 1st occurrence of >10 for Apple #1

4/1/2018 Apple #1 100 3/1/2018

5/1/2018 Apple #1 0 3/1/2018

6/1/2018 Apple #1 50 3/1/2018

7/1/2018 Apple #1 200 3/1/2018

8/1/2018 Apple #1 150 3/1/2018

1/1/2018 Apple #2 100 1/1/2018 * This date is the 1st occurrence of >10 for Apple #2

2/1/2018 Apple #2 200 1/1/2018

3/1/2018 Apple #2 75 1/1/2018

4/1/2018 Apple #2 100 1/1/2018

5/1/2018 Apple #2 0 1/1/2018

6/1/2018 Apple #2 50 1/1/2018

7/1/2018 Apple #2 200 1/1/2018

8/1/2018 Apple #2 150 1/1/2018

1/1/2018 Pear #2 3 2/1/2018

2/1/2018 Pear #2 200 2/1/2018 * This date is the 1st occurrence of > 10 for Pear #2

3/1/2018 Pear #2 75 2/1/2018

Hopefully that makes sense.

I am guessing I need to be using some sort of OVER function, but I am not familiar with how to use them.

Thanks in advance for any tips! :)

[Gaia Paolini]

Hi

try this:

Max(If(([Value] - 10)>0,Min([Date]) OVER ([EntityName],[Dataset]),NULL)) OVER ([EntityName],[Dataset])

the expression inside the IF(..) would only output a value when VALUE>10, so it is wrapped inside the MAX(..) OVER ([EntityName],[Dataset]) to spread it across all rows, since max of X against null is always X.

Gaia

[Brian Richards]

Excellent and elegant solution. This helped solve a similar problem I was having. Thanks!