Visualizing Employee Capacity

[TobiasTebben]

Dear community,

I am having difficulties with the following task. 

My first data table is a list of employees:

Id	Name
1	Miller
2	Smith

My second table is a list containing the working hour models of the employees:

Id	HoursPerWeek	ValidFrom	ValidUntil
1	40	2022-01-01
2	40	2022-01-01	2022-06-30
2	30	2022-07-01

My ultimate goal is to create a bar chart with the employee capacity per week for the whole year, i.e. all weeks up until 07-31: 80 hours and all following weeks 70 hours.

I am failing miserably with this, both with a visualization based on the two tables shown, and with creating a new "capacity table" in the load script.

I would be very grateful for any advice!

Thanks in advance
Tobias

[deepanshuSh]

Hi Tobias, Not sure about the model that you are currently using in detail. So i am commenting based on the provided tables as the actual data, you can join the two tables by doing a left join on the second table containing the working hours. 

i.e 

Working_hour_model:

Load * ;

Left join 

Employees:

ID, name;

And then using the same on the front end, even if you don't want to do the left join, due to the association property of the qlik, there would be direct link between the two tables and can be used directly for calculation. 

Calculation:

Sum ({<ValidFrom>={"2022-01-01"},ValidUntil={Put date here for validity ending}>}HoursPerWeek)

[TobiasTebben]

Thank you, DeepanshuSh,

maybe problem lies with the visualization.  Remember, I wanted to create a bar chart with calendar weeks as my dimension (x-axis) and the (sum of) work hours as measure (y-axis). If I had a table that contained the weekly hours for all employees and for all weeks of the year, I could create this bar chart for sure. 

CalendarWeek	Id	Hours
1	1	40
1	2	40
2	1	40
2	2	40
etc.

But as of now, I do not have such a table.

So what is the dimension in my bar chart? Is this what "calculated dimensions" are used for (I only know the term, not how to use them in this scenario, hence my question)? Or do I need to create a table like the one described above in my load script that "multiplies out" all the ValidSince/ValidUntil dates for all employees for the whole year? I am failing to do this.

I hope I was able to describe my problem in a comprehensible way.

Thanks again!

[deepanshuSh]

So you could create a master calendar out of the date mentioned in valid from and valid until in the backend and use that for the dimensions (x-axis) and sum of hours on y axis. The link between two tables can be created as per my earlier suggestions. 

A calculated dimension is an modified dimension on the front end, which means using specific conditions the data can be modulated.