How to add customized bar on top of stacked chart?

[songzixian2016]

Hi all,

I have a time tracking dataset below indicating hours employee tracked per month:

Month	Hour	Emp
July	1	A
July	2	A
July	3	B
July	4	C
August	2	A
August	8	B
August	4	B
August	1	C

I made a stacked bar chart below:

 

I would like to add an additional bar on top of each bar to show untracked hours. In this case, I have another table indicating the total hours supposed to be tracked.

Month	Total
July	20
August	18

And I would like a chart like below:

 

Could anyone help me with the loading script logic to generate this chart? Thanks!

[skamath1]

You can concat the second table to the first one as to force the 2 tables as one.

Concatenate[ (table1 ]  

Load Month, Total as Hour, 'U/T' as Emp from table 2 ;

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

[songzixian2016]

To build the chart I would like, I need a row [Total - sum(hour)] /**sum(hour) is the total tracked hour for that month by every employee**/ instead of just concat total to the original table. How can I get this manipulated?

[Zhandos_Shotan]

Hi!

You need to create Employee row as Untracked hour value.

Create table with aggregated untracked hours as Employee field:

UTH:

noConcatenate load

Month,

'UNTRACKED' as Emp,

 [Total - sum(hour)]  as Hour

resident Tab1

group by Month;

//Then add it as new row

Concatenate(Tab1) load

Month,

Emp,

Hour

resident UTH;

Best regards

[songzixian2016]

I tried this: But result said Field not found: Total. Could you help me? thank you!!

TABLE1:

LOAD * INLINE [

Month, Hour, Emp

July, 1, A

July, 2, A

July, 3, B

July, 4, C

August, 2, A

August, 8, B

August, 4, B

August, 1, C

];

//left join(TABLE1)

TABLE2:

LOAD Month, Total INLINE [

Month, Total

July, 20

August, 18
];

UTH:
NoConcatenate load
Month,
'Untracked' AS Emp,
Total-sum(Hour) AS Hour
Resident TABLE1
group by Month;

Concatenate(TABLE1) load
Month,
Emp,
Hour
Resident UTH;

[Zhandos_Shotan]

After loading tab1, tab2, join total field to main table:

Join(TABLE1)
Month,
Total
Resident TABLE2;