Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to calculate the number of staff allocated to each sessions (based on the unique "Room" and the "Session_Start" combination).
Please find the below base data set,
Trainee No | Trainee Name | Staff 1 | Staff 2 | Staff 3 | Room | Session_Start | Date |
100 | Mark | Matt | Room-01 | 2/10/2018 14:30 | 2/10/2018 | ||
101 | Anton | Sylan | Room-02 | 3/10/2018 10:30 | 3/10/2018 | ||
102 | Andrew | Matt | Sylan | Room-01 | 2/10/2018 14:30 | 2/10/2018 | |
103 | Ash | Scod | Room-03 | 5/10/2018 10:30 | 5/10/2018 | ||
104 | Prish | Scod | Brenden | Room-01 | 12/10/2018 14:30 | 12/10/2018 | |
105 | David | Matt | Sylan | Brenden | Room-01 | 2/10/2018 10:30 | 2/10/2018 |
101 | Anton | Sylan | Room-03 | 5/10/2018 10:30 | 5/10/2018 | ||
103 | Ash | Scod | Brenden | Room-01 | 2/10/2018 14:30 | 2/10/2018 | |
105 | David | Scod | Room-01 | 16/10/2018 14:30 | 16/10/2018 | ||
101 | Anton | Paul | Room-01 | 2/10/2018 10:30 | 2/10/2018 | ||
100 | Mark | Scod | Brenden | Room-03 | 5/10/2018 10:30 | 5/10/2018 | |
101 | Anton | Sylan | Room-01 | 16/10/2018 14:30 | 16/10/2018 | ||
105 | David | Scod | Sylan | Room-01 | 10/10/2018 14:30 | 10/10/2018 |
As depicted above, each session will have number of Trainee Staff and assigned Staff members for the training programs.
My requirement is to calculate the total number of allocated staff for each session (considering the unique key as the combination of "Room" and "Session_Start") and construct an outcome table as shown below;
Session | Session_Start | Number of Staff |
Room-01 | 2/10/2018 10:30 | 4 |
Room-01 | 2/10/2018 14:30 | 4 |
Room-01 | 10/10/2018 14:30 | 2 |
Room-01 | 12/10/2018 14:30 | 2 |
Room-01 | 16/10/2018 14:30 | 2 |
Room-02 | 3/10/2018 10:30 | 1 |
Room-03 | 5/10/2018 10:30 | 3 |
Appreciate, if someone could help me in this regards.
Note
I can perform the data load part and the grouping of the "Trainee No" based on the "Room" and the "Session_Start" field but not so sure how to use the SET analysis to accomplish the above requirement using an expression.
Look forward to hearing from you.
Thank you in advance.
Kind regards,
Andy
Try like:
RangeSum(count(DISTINCT [Trainee No]),count(DISTINCT [Staff 1]),
Count(DISTINCT [Staff 2]), Count(DISTINCT [Staff 3]))
If you could use crosstable() and transform your table, set analysis might not be required. PFA
CrossTable(StaffType, StaffName, 3)
LOAD Room,
Session_Start,
Date,
[Trainee Name],
[Staff 1],
[Staff 2],
[Staff 3]
FROM
[Staff Allocation.xlsx]
(ooxml, embedded labels, table is [Group Activity]);
Try like:
RangeSum(count(DISTINCT [Trainee No]),count(DISTINCT [Staff 1]),
Count(DISTINCT [Staff 2]), Count(DISTINCT [Staff 3]))