CODEWITHSUNDEEP
calculated-columnsspotfire
Brittany Enfield
Brittany Enfield

Reputation: 107

Identify earliest date in a group; Save value associated with that date

I am trying to identify the earliest date in a grouping in Spotfire. I included a screenshot of a sample table of data.

Basically, here are the headers:

MFG_AREA_NAME, LOT_ID, DATE_STEP_STARTED, DATE_STEP_FINISHED, MFG_ROUTE_NAME, START_QTY, END_QTY

For each unique MFG_AREA_NAME, LOT_ID, MFG_ROUTE_NAME combination, I want to identify the earliest date and copy the START_QTY associated with that date.

So, using the sample table, for ROUTE=AC, I want to grab 576. For ROUTE=FT, I want to grab 560 and so on.

I know I'll need to use the OVER function to group them, but I can't figure out the date/qty part.

Thanks!

Sample Table

Upvotes: 1

Views: 443

Answers (1)

S3S
S3S

Reputation: 25122

The OVER() usage you are looking for is INTERSECT().

Something like...

IF([DATE_STEP_STARTED] = MIN([DATE_STEP_STARTED) OVER (INTERSECT([MFG_AREA_NAME], [LOT_ID], [MFG_ROUTE_NAME])),[START_QTY])

Upvotes: 1

Related Questions