Calculated column from two different tables

[mxblaise]

Hello,

There are several post about this topic but I really don't get the logic.

I have two tables feeded by two different files, so two corresponding tables.

OperationsTable :

Serial	Operation	Operation date
1	Injection	02/01/2018
1	Machining	04/01/2018
1	Visual control	07/01/2018
2	Injection	03/01/2018
2	Machining	04/01/2018
2	Visual control	08/01/2018
3	Injection	03/01/2018
3	Machining	04/01/2018
3	Visual control	10/01/2018

NDTTable :

Serial	NDT date
1	07/01/2018
2	10/01/2018
3	11/01/2018

The link between both serial field

In "visualisations" tab, I am doing a "table", in which I check all the fields.

I would like now to comupte in a new column litteraly : NDT date - Operation date WHERE Operation = "Injection"

But i dont even know if I should add a collumn to my "visualisation table", or create a new "data table" in which I can do this query and then call it in my "visualisation table".

Thanks a lot!

Max

[Greg_Deckler · Accepted solution]

I believe that this will work:

Column = DATEDIFF([NDT date],MAXX(FILTER(RELATEDTABLE(OperationsTable),[Operation]="Injection"),[Operation date]),DAY)

What this is doing:

RELATEDTABLE - brings back all related records from OperationsTable

FILTER - Filters the results from RELATEDTABLE to just those whose [Operation] is "Injection"

MAXX - Grabs the MAX out of a table, could use any aggregation

DATEDIFF takes the difference in days between the two dates

[Martin456]

Hey,

I have similar problem and this solution doesn't work as I am getting and error of too many arguments. I have table A with serial number and pending status on a certain date. Second table B with serial number completed status on a certain date and would like to count how many days have passed between pending and completed status.

Thanks a lot in advance.

[Martin456]

Found out I was missing some parenthesis. The formula works just fine.

[mxblaise]

Thanks smoupre!

I undrestand a bit better the logic behind. Do you know a good tutorial about this kind of formulas?

Max