How to do math across multiple tables and get value ?
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 ?
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.
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.
