Naming an input field OR adding two calculated values from separate data tables

I have had a very difficult time finding a solution to what I think would be a simple problem:

Basically, I am looking to find a way to add two calculated values together, but they are on two separate data tables without an efficient way to combine them.

Below is a quick/similar example to my problem:

Data Table A is used to calculate the total number of people hired for a company. It uses candidate data to accomplish this. I have a calculated column that states if a candidate is in the status 'Hired' then enter 1, else 0. I then have a calculated value that takes the sum of all the candidates that are hired. This is further filtered by going into the Properties->Data tab and limiting the data using an expression. In the expression I have a propery control attached that limits the data to a certain date frame, based on the Candidate's Last Status Change. In this example I will say that the results return a total number of hires = 200.

In Data Table B, we have data for the Job as opposed to the candidate. For this data table I want to calculate the total number of jobs that were cancelled. This is accomplished by creating a calculated column that says 'If Status = "Cancelled", 1, else 0. I then use a calculated value that takes the sum of all the cancellations similar to above. And again, the data is limited using an expression with a property control that filters the data based on a certain date frame.. but this date frame is based on the Job's Closed Date. In this example I will say that the results return a total number of cancellations = 50.

I have tried multiple ways of combining the tables but they do not effectively match up in an accurate way so my next option is to find a way to simply add the two calculated values together to reach a total of 250. I have looked all over and not been able to find a clear solution on how to simply take the two calculated values and add them together. Is there a way to do this? Or, another possible workaround is if it's possible to name an input field similar to a property control. If I can name the input field, then I could use an expression/calculated value to simply add the inputs (similar to selecting a property control) together.

 

Thank you to any insights you can provide!

(1) Answer

Login