topic Re: How to do math across multiple tables and get value ? in Connectivity & Data Prep

How to do math across multiple tables and get value ?

HuggyBear — Mon, 06 Nov 2023 15:41:51 GMT

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 !

Re: How to do math across multiple tables and get value ?

WaltShpuntoff — Mon, 06 Nov 2023 15:56:33 GMT

Look into concatenating the tables together where they share dimensions.

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

-ws

Re: How to do math across multiple tables and get value ?

vincent_ardiet_ — Mon, 06 Nov 2023 15:57:42 GMT

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?

Re: How to do math across multiple tables and get value ?

marcus_sommer — Mon, 06 Nov 2023 15:59:51 GMT

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.

Re: How to do math across multiple tables and get value ?

HuggyBear — Mon, 06 Nov 2023 16:02:54 GMT

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.

Re: How to do math across multiple tables and get value ?

vincent_ardiet_ — Mon, 06 Nov 2023 16:08:46 GMT

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.

Re: How to do math across multiple tables and get value ?

marcus_sommer — Mon, 06 Nov 2023 16:12:31 GMT

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.

Re: How to do math across multiple tables and get value ?

HuggyBear — Mon, 06 Nov 2023 16:27:38 GMT

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.

Re: How to do math across multiple tables and get value ?

vincent_ardiet_ — Mon, 06 Nov 2023 16:38:56 GMT

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?

Re: How to do math across multiple tables and get value ?

HuggyBear — Mon, 06 Nov 2023 18:29:09 GMT

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.

Re: How to do math across multiple tables and get value ?

marcus_sommer — Tue, 07 Nov 2023 07:58:05 GMT

It's an illusion to expect that the Insight Advisor (like all the other hyped AI tools/logic) could solve challenges for you. If you could do everything yourself you would comprehend the capabilities of the tool and then you may use it as a valuable wizard to save time and efforts.

What you are describing is to produce multiple views in regard to current- and budget-data in various granularity. (Nearly) everything should be possible with the shown table-structures - but not with a single object and/or a global expression else each view needs to be designed individually.

Therefore I suggest to start classically by creating n table-charts (one after another) with the relevant dimensions and building the expressions step by step. Even if it seemed a bit cumbersome - it's not because it are mostly necessary steps to validate the data-quality and the logic.

Re: How to do math across multiple tables and get value ?

vincent_ardiet_ — Tue, 07 Nov 2023 08:15:49 GMT

Yes start slowly.
Put the project as a dimension, then an expression with sum(Budget).
Then another expression with sum(duration), you will see that even if a user is affected on different project, this is hopefully respected by Qlik.
The engine is also handling the cardinality between tables on a different manner as an SQL database which is more natural.
Play with different measures and see the results of combinations.