Sum coming wrong when joining 2 tables

[tvisha]

Hi,

I have 2 tables,

One with Course Registration Details and Duration and Other with Employee info,

Now when I join the two tables, the sum is coming wrong, the tablese are joined using a third table, registration, where

table - key
course - registration table (Course id column as key)
registration - HR Table (HR Id key)

Course have details as
Courseid    Duration
5                  15
11                10

Registration
Courseid         hr_id
5                     91
5                     92
11                   10
11                  12

The  total duration should be, 50 hours, however it is calculating it only once as 25 hours. If someone can know what I am doing wrong.

[Pablo007]

Hi @tvisha ,

the duration field is only on the shortest Table,  so that it is making the SUM of what it has. 2 registers 15+10

You have to change the Logic,  just working with HR table and using course details table to make a mapping or to multiply creating a new row. on  HR table 

[Or · Accepted solution]

As explained by @Pablo007 , your issue is that the current sum only reads data from the Course table, so it has no reason to think you intend to multiply the duration by the number of hr_ids (registrants?). Note that this is in no way wrong - Qlik is doing what it's told, but your expectation doesn't match that.

You could approach this in several ways...

1) Bring the Duration field into the Registration table so you can sum it directly or otherwise modify your schema to allow you to write simple aggregations on the desired fields

2) Force the formula to evaluate the desired granularity by using something like Sum(Duration * (hr_id/hr_id)), or add a field in the Registration table that is just 1 as Course_Count and then use Sum(Duration * Course_Count)

3) Use Aggr() to tell Qlik what exactly you're trying to sum/count

Ideally you get the schema to work for your requirements. The second option typically works fine but is harder for developers to read in the future (and harder for users to understand if you have self-service). The third option is easier to read but may have negative impact on performance.

[tvisha]

Thank you, I thought I need to use some formula, but I assumed qlik will add it. I will try the above

[tvisha]

Aggr(Sum(Duration),[HR_ID-hr_id]) 

gives me all 0

[tvisha]

Used the 2nd logic and it worked. Thank you