Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Marcos_S
New 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
Valued Contributor

Re: Sum between dates when month is a dimension

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]);
2 Replies
Sergey_Shuklin
Valued Contributor

Re: Sum between dates when month is a dimension

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
New Contributor

Re: Sum between dates when month is a dimension

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