Drill down for Grouped bar charts

[chl98]

Hi all,  thank you for taking the time to look at my question. 

Here are the data I am currently working with 

Estimated_data:
ESTIMATED_DAYS
ACTIVITIES 
TEAMS_ESTIMATED

Actual_data:
ACTUAL_DAYS 
ACTIVITIES 
TEAMS_ACTUAL 

What I am trying to achieve: 
A bar chart to compare the days by activities, then drilled down by teams. 
This is an image to compare the days by activities: 

However when I tried to create a drill-down dimension
1. ACTIVITIES 
2. TEAMS_ESTIMATED

The drill down does not work for the actual_days, only for estimated_days.  I have changing the TEAMS_ESTIMATED and TEAMS_ACTUAL  to the same field name, however Qlik Sense created synthetic key and the data is not current. 

How can I achieve the results I want ? 
Thank you in advance, 

Cheers, 
CHL

[stevejoyce]

It's not clear enough how your data should be, but activities and teams sound like they should be common.

if it makes sense to combine the two tables, you can do a) join the tables b) concatenate the tables or c) have a link table (linkkey, activities, teams) and remove the duplicate columns in your estimate and actuals data.

I would try joining the 2 tables, i assume they are joined on ACTIVITIES and TEAMS. 

Estimated_data:
ESTIMATED_DAYS
ACTIVITIES 
TEAMS_ESTIMATED  as TEAMS

from Estimated_data;

outer join (Estimated_data):
ACTUAL_DAYS 
ACTIVITIES 
TEAMS_ACTUAL as TEAMS

from Actuals_data;

drop tables Actuals_data;

[Joseph_Musekura]

Agreed with Steve's comments and solution approach
There is no need to keep TEAMS-ESTIMATED   and TEAMS-ACTUAL as separate fields while they have the same data (provide same information).
/joseph

[chl98]

Hi, thank you for your suggestion.

A little bit more details on the data:

As there are multiple row of similar Activities and Teams, I am afraid that using outer join might not be correct since there are multiple matching rows.

Thank

[stevejoyce]

Are you sure there are multiple rows the combination of Activity + Team?  I don't see that in your sample data.

The join i sent was joining on both fields.

Otherwise concatenate or use link table.

[chl98]

Hi Steve, 

Thanks for the reply. 

For example in the estimation table, there are 2 instances of ACTIVITY A and TEAM_1. These data are just examples, in fact there are actully multiple rows where by the ACTIVITY and TEAM are the same. Hence, I am not sure whether join will work for this case. 

Is there any other solution ? 

Thanks !

[stevejoyce]

In this case you can either aggregate at the activities + team level with sum/group by in your load script.  Or if you need to keep granular level and/or have other columns you didn't post, you can use a link table between these 2 tables with unique activities+teams and keep existing granularity.

Estimated_data:
load *,
ACTIVITIES & '|' & TEAMS_ESTIMATED as %Link_Activities_Teams
;
load * inline [
ESTIMATED_DAYS, ACTIVITIES, TEAMS_ESTIMATED
1, A, Team_1
4, B, Team_1
2, A, Team_2
4, A, Team_1
]
;

Actual_data:
load *,
ACTIVITIES & '|' & TEAMS_ACTUAL as %Link_Activities_Teams
;
load * inline [
ACTUAL_DAYS, ACTIVITIES, TEAMS_ACTUAL
10, C, Team_2
2, C, Team_4
50, A, Team_1
25, B, Team_2
]
;

link_table:
load distinct
%Link_Activities_Teams,
ACTIVITIES,
TEAMS_ESTIMATED AS TEAMS
resident Estimated_data;

concatenate(link_table)
load distinct
%Link_Activities_Teams,
ACTIVITIES,
TEAMS_ACTUAL AS TEAMS
resident Actual_data;

drop fields ACTIVITIES, TEAMS_ESTIMATED from Estimated_data;

drop fields ACTIVITIES, TEAMS_ACTUAL from Actual_data;

AUTONUMBER %Link_Activities_Teams;