Forum Discussion
Calculated column from two different tables
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
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
4 Replies
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
Thanks smoupre!
I undrestand a bit better the logic behind. Do you know a good tutorial about this kind of formulas?
Max
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.
Found out I was missing some parenthesis. The formula works just fine.
