Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 ;
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?
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
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;