How to do math across multiple tables and get value ?

[HuggyBear]

Hello, 
I m struggling how to resolve mathematical issue in Qlik  for some time h

equation is simple : 
Budget - (Project_members n+1 * cost_rate * duration_in_their_timesheets) = "remaining budget"
This is simple equation which I can resolve on the paper easily in couple seconds.

Project_budget is in table, where I dont have timesheet entries and project members.
Project_members are in different table than timesheet entries. 

All tables has common columns like project_title and project_id, with same data values
So the issue  - if I m "creating new field" it only allows me to do that in specific tables, but not get data from the other table. 
So how do I get calculation like that above ?

Thanks for your help ! 

[WaltShpuntoff]

Look into concatenating the tables together where they share dimensions. 

Once they are in the same table, those calculations will be easier.

-ws

[vincent_ardiet_]

And you want to create this in the datamodel, not to compute this on the fly in a chart right?
In this case, could share the structure of your different tables?

[marcus_sommer]

Within the script you need to pull all needed fields/information into a single table - maybe with some mapping-tables.

Simpler might be to do this calculation just within the UI - if the associations of the tables are suitable it's quite so simple like above described.

[HuggyBear]

Well, if that can be calculated in the overview and sheets on the fly, that would be awesome, but I dont have idea how to do that. 

Thing is that there is multiple projects - each of them has associated multiple members and each of these members has different cost rate and hours logged in the time sheet table.

[vincent_ardiet_]

If you put the project in dimension, you can then use an expression like this:
Sum(Budget) - (Count(Distinct Project_members)+1) * Sum(cost_rate * duration_in_their_timesheets))
But honestly this could be totally wrong depending on your datamodel.
Ideally if you could share 5-6 rows of each tables with dummy data, this will be much easier.

[marcus_sommer]

In general could this be considered with something like:

sum(aggr(YourCalculation, Project, Member))

whereby the calculation would be done on a project and member level and those results added (you may need further dimensionality in the aggr - maybe any period-information or whatever might relevant). By larger data-sets and/or complex objects such an approach could be slowdown the performance - but it's so simple that you could just try it before doing it within the script-level. 

[HuggyBear]

This is pulled directly from the SQL database :

One table is showing all projectSecond table is who is working and assigned to the project and their cost rates
And the last table is showing who is associated under current project.
This is pulled directly from the SQL database.

[vincent_ardiet_]

I don't understand why you are multiplying with the number of employees.
If you put projectid in dimension, and Sum(budget)-Sum(costamount*duration) as a measure, this should gives you your remaining budget isn't?

[HuggyBear]

Well, the idea from management was also to have an overview of who had the biggest impact based on the working hours to cost rate ratio, etc. You know, just to have a more granulated overview in Qlik. Another thing is that there are multiple people with different rates working under the same project. I understand that I can sum all the cost rates for a specific project and multiply by the hours spent on that project, but management would like to see something like, 'Okay, now show us how much of the budget was consumed by John and Mark from the entire pie.'

But even if I wanted to implement that simpler function, where and how do I add it? This is all that I'm getting from the Insight Advisor, but I'm not sure where to find and add these functions.