Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
songzixian2016
Contributor III
Contributor III

How to add customized bar on top of stacked chart?

Hi all,

 

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

MonthHourEmp
July1A
July2A
July3B
July4C
August2A
August8B
August4B
August1C

 

I made a stacked bar chart below:

11111.png 

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.

MonthTotal
July20
August18

 

And I would like a chart like below:

2222.png 

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

Labels (1)
5 Replies
skamath1
Creator III
Creator III

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
Contributor III
Contributor III
Author

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
Partner - Creator II
Partner - Creator II

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
Contributor III
Contributor III
Author

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
Partner - Creator II
Partner - Creator II

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

Join(TABLE1)
Month,
Total
Resident TABLE2;