Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Marcos_S
Contributor
Contributor

Sum between dates when month is a dimension

Hello, I am not able to solve this challenge: I have a table of people (see attachment) whith Employee ID, Profile, Date In, Date Out, and Number of employees. If we know the employee, we have an ID and the number of employees is 1. If we only know the profile we will need, we do not have an ID and the number of empoyees can be any number.

I need to show a graph per month and per profile, with the number of active employees: so I have to sum the number of employees each month if Date In is before the month and Date Out is after the month.

Any idea?

Many thanks.

Labels (3)
1 Solution

Accepted Solutions
Sergey_Shuklin
Specialist
Specialist

Hello!

Is this solution suitable for you?

mon_emp_graph_pic1.png

Script improving:

LOAD Year, 
     [Employee ID], 
     Profile, 
     [Date In],
     [Date Out],
     AddMonths(MonthStart([Date Out]),-1) as rep_date,
     [Number of employees]
FROM
People.xlsx
(ooxml, embedded labels, table is [Initial table]);

View solution in original post

2 Replies
Sergey_Shuklin
Specialist
Specialist

Hello!

Is this solution suitable for you?

mon_emp_graph_pic1.png

Script improving:

LOAD Year, 
     [Employee ID], 
     Profile, 
     [Date In],
     [Date Out],
     AddMonths(MonthStart([Date Out]),-1) as rep_date,
     [Number of employees]
FROM
People.xlsx
(ooxml, embedded labels, table is [Initial table]);
Marcos_S
Contributor
Contributor
Author

Thanks. I have to test it. I did not realized that I can load the data in a different way in order to show the graph.

Many thanks