Join node shows different results compared to underlying aggregation

[former_member236529]

Hi there

I am working on a requirement where the weights have to be created and rolled up based on counts of certain parameters - such as Goals, Objectives, Key initiatives, Activities and Responses.

A Goal can have multiple Objectives, an objective can have multiple Key initiatives and so on.

I started aggregating from the lowest level for the count for this, but while it works on the lowest level, the second level (from bottom) shows me weird results. I see good values in the aggregation level, but that's not what I see after the join.

Aggregation 2 and join 2 are the ones in question. The column I am concerned about, is COUNT_ACTIVITY

For Goal 1, Objective 1 and Key initiative 1, the count from Aggregation 2 returns 2, which is the expected value.

But when I join it back (join_2), I see different values for the same variable. Please see the screenshot below.

For Goal 1, Objective 1 and Key initiative 1, the count from Aggregation was 2, but from join, I get 4.

I was expecting to see 2 (from the aggregation) against all rows corresponding to Goal 1, Objective 1 and Key initiative 1.

Am I doing something wrong here? I haven't come across something like this earlier.

Thanks in advance!

[varma_narayana]

Hi Sajith,

As I understand the problem from the given screenshots, I believe the reason for the data issue at Join 2 node is as explained below:

It seems the Join1 node has multiple entries for a combination of Goal, Objective and Key initiative - where the other fields like Activityid, responseid could be different (Example: Goal 1, Objective 1 and Key initiative 1 may have two activityid values). In that case it will lead to the multiplication of measure values where the aggregation is set to "Sum".

Please check this by displaying the other attributes (such as Activityid, responseid) also at the Join2 node level data preview.

Hope this helps.

regards,

Varma