Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
t_hylander
Contributor

Handling staff in Qlikview

Hi,

I'm making a staff-app but have encounterd a little problem regarding staff that end their employment.

Lets say I have a table similar to this;

Staff_tmp:
LOAD * INLINE [
    Id, Name, Hired_date, End_date, Value
    1, Arnold, 2018-01-01, , 8
    2, Steven, 2018-01-01, , 8
    3, Cloude, 2018-01-01, , 8
    4, Emma, 2018-01-01, , 8
    5, Sara, 2018-01-01, 2018-06-01, 8
    6, Donald, 2018-01-01, , 8
    7, Louise, 2018-01-01, , 8
    8, Roland, 2018-01-01, 2018-10-01, 8
    9, Thomas, 2018-01-01, , 8
 ];

 

 

What I want is;

1. When the user select ex. july, a textlabel show the number of employees that month (8 since Sara ended in june).

Thanks in advance!

1 Solution

Accepted Solutions
Partner
Partner

Re: Handling staff in Qlikview

 

count( {< 
Hired_date = {"<=$(=min(%Date))"} ,
End_date= {">=$(=max(%Date))", ''}
>} distinct Id)

This expression will work on the script below given that you have a data island calendar with a date field called %Date. The tricky part here is to fetch the empty End_date. You can not use this if the value is NULL(). I would as in my earlier example set the value to today or tomorrow for the field used in the set. 

Staff_tmp:
LOAD * INLINE [
Id, Name, Hired_date, End_date, Value
1, Arnold, 2018-01-01, , 8
2, Steven, 2018-01-01, , 8
3, Cloude, 2018-01-01, , 8
4, Emma, 2018-01-01, , 8
5, Sara, 2018-01-01, 2018-06-01, 8
6, Donald, 2018-01-01, , 8
7, Louise, 2018-01-01, , 8
8, Roland, 2018-01-01, 2018-10-01, 8
9, Thomas, 2018-01-01, , 8
];

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

6 Replies
Partner
Partner

Re: Handling staff in Qlikview

You need some kind of master calendar in your datamodell containing all dates that you are interested to analyse. If you want help creating one there are a lot of examples available in the community or Google search.

With the calendar you can do an intervalmatch. See script below.

Staff:
LOAD Id, Name, Hired_date, alt(End_date, today()) as End_date, Value INLINE [
Id, Name, Hired_date, End_date, Value
1, Arnold, 2018-01-01, , 8
2, Steven, 2018-01-01, , 8
3, Cloude, 2018-01-01, , 8
4, Emma, 2018-01-01, , 8
5, Sara, 2018-01-01, 2018-06-01, 8
6, Donald, 2018-01-01, , 8
7, Louise, 2018-01-01, , 8
8, Roland, 2018-01-01, 2018-10-01, 8
9, Thomas, 2018-01-01, , 8
];

MasterCalendar:
Load
Date,
Month,
Year,
etc...
From calendar;

IntervalMatch(Date)
LOAD Hired_date, End_date
Resident Staff;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
t_hylander
Contributor

Re: Handling staff in Qlikview

Sorry for not pointing that out, i DO have a master calender. I see I was a bit unclear (it was very clear in my head Smiley Wink ).
What I need help with is the set analysis to show number of employees at a certain month taken account for when they start and end their employmeent.
Somthing like this (I know its wrong but just to show you want I need);

Count({<%Date<=End_date + %Date>=Start_date>} distinct ID)
Partner
Partner

Re: Handling staff in Qlikview

 

count( {< 
Hired_date = {"<=$(=min(%Date))"} ,
End_date= {">=$(=max(%Date))", ''}
>} distinct Id)

This expression will work on the script below given that you have a data island calendar with a date field called %Date. The tricky part here is to fetch the empty End_date. You can not use this if the value is NULL(). I would as in my earlier example set the value to today or tomorrow for the field used in the set. 

Staff_tmp:
LOAD * INLINE [
Id, Name, Hired_date, End_date, Value
1, Arnold, 2018-01-01, , 8
2, Steven, 2018-01-01, , 8
3, Cloude, 2018-01-01, , 8
4, Emma, 2018-01-01, , 8
5, Sara, 2018-01-01, 2018-06-01, 8
6, Donald, 2018-01-01, , 8
7, Louise, 2018-01-01, , 8
8, Roland, 2018-01-01, 2018-10-01, 8
9, Thomas, 2018-01-01, , 8
];

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

t_hylander
Contributor

Re: Handling staff in Qlikview

Close enough, it should be max(%Date) on Hired_date as well.
I also skipped Null-values and used Today() as suggested!

Thanks!
Partner
Partner

Re: Handling staff in Qlikview

I'm happy to help.

If you use min(Date) you get people that where hired for the whole selected period. If you use max(Date) you get people hired the max date in your selection. When selecting a single date the outcome will be identical.

The combination max(Date) for Hired_date and min(Date) for End_date will return any staff hired inside the selected period.
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

Re: Handling staff in Qlikview

Another approach, using a generated Fact table and the aggr() function.