how to you write a calculated column with Max (of 2 columns) over (JOB) and returns the value in "row 4"

[eddy ng]

Details of the table are in the attached,

[column JOB] [time stamp] [time sequence] [tonnes]

A 1/1/2020 1 5000

A 1/1/2020 2 6000

B

C

C

C

 

[outcome i want from the calculated column)= Max [time stamp] and Max[time sequence]over [column job], return [tonnes]= "6000"

[Shashank Gurnalkar]

Try this expression. I am getting the expected output with your sample data.

If(([time stamp]=Max([time stamp]) Over ([column JOB])) and ([time sequence]=Max([time sequence]) Over ([column JOB])),[tonnes])

[eddy ng]

Sorry doesnt work,

 

Because, max time stamp over job will not always intersect with over all max time sequence, hence coming up with null for everything.

 

We want to have max timestamp over job, and then max time sequence of the max timestamp- (if that makes sense)..

 

example: max timestamp (23/04/2020 ) will result in 3 rows (that has the same timestamp)

 

Within the 3 rows there will be a max time sequence (listed 1,2 &3)

 

I want time sequence= 3 that are on rows with 23/04/2020.

 

If you try to get max of both, there will be higher sequence on other dates- hence coming up with null.

 

 

 

 

[Shashank Gurnalkar]

Sorry, I misread it. This can be achieved using nested Case statements like:

 

Case [time stamp] When Max([time stamp]) OVER ([column JOB]) then Case [time sequence] When Max([time sequence]) OVER ([column JOB]) then [tonnes] End End

 

See the test column below: