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: 
Anonymous
Not applicable

Number of FTE's remaining

Hi,

I have a dataset with consultants with a start and end date.

I would like to visualize how many consultants that are available for each month.

How can this be calculated in Qlik Sense?

So my dataset could look like this:

  

FTEStartDateEndDate
101/01/201731/05/2017
201/02/201731/10/2017
201/03/201730/09/2017
101/03/2017

31/12/2017

How can I calculate in Qlik, that I have 1 FTE available in January, 3 in February (as one is hired in January with end date in May and two more are hired in February with end date in October) and so forth?

Result

                                                  

MonthFTE
Jan1
Feb3
Mar6
Apr6
May6
Jun5
Jul5
Aug5
Sep5
Oct3
Nov1
Dec1
MonthFTE
Jan1
Feb3
Mar6
Apr6
May6
Jun5
Jul5
Aug5
Sep5
Oct3
Nov1
Dec1

Sample data is attached

Br,

Thomas

1 Solution

Accepted Solutions
sunny_talwar

May be like this in the script

Table:

LOAD *,

  Date(StartDate + IterNo() - 1) as Date,

  Month(StartDate + IterNo() - 1) as Month,

  Year(StartDate + IterNo() - 1) as Year,

  Date(MonthStart(StartDate + IterNo() - 1), 'MMM-YYYY') as MonthYear

While StartDate + IterNo() - 1 <= EndDate;

LOAD * INLINE [

    FTE, StartDate, EndDate

    1, 1/1/2017, 5/31/2017

    2, 2/1/2017, 10/31/2017

    2, 3/1/2017, 9/30/2017

    1, 3/1/2017, 12/31/2017

];

and then this

Dimension

Month

Expression

Sum(Aggr(Sum(DISTINCT FTE), Month, StartDate, EndDate))

Capture.PNG

View solution in original post

8 Replies
sunny_talwar

May be like this in the script

Table:

LOAD *,

  Date(StartDate + IterNo() - 1) as Date,

  Month(StartDate + IterNo() - 1) as Month,

  Year(StartDate + IterNo() - 1) as Year,

  Date(MonthStart(StartDate + IterNo() - 1), 'MMM-YYYY') as MonthYear

While StartDate + IterNo() - 1 <= EndDate;

LOAD * INLINE [

    FTE, StartDate, EndDate

    1, 1/1/2017, 5/31/2017

    2, 2/1/2017, 10/31/2017

    2, 3/1/2017, 9/30/2017

    1, 3/1/2017, 12/31/2017

];

and then this

Dimension

Month

Expression

Sum(Aggr(Sum(DISTINCT FTE), Month, StartDate, EndDate))

Capture.PNG

Anonymous
Not applicable
Author

Maybe have a look at the IntervalMatch() function IntervalMatch

It works exactly the same in Qlik Sense as in QlikView.

Anonymous
Not applicable
Author

I cannot get it to work. Not on my own dataset, and not even if I copy paste your script and calculation into an empty app.

The calculation just show me 3 in every month.

Anonymous
Not applicable
Author

Seems like it had something to do with the date format, so I got it to work with your script. Now I just need to get it to work with my own dataset.

sunny_talwar

How does your date field look like?

Anonymous
Not applicable
Author

My date field is dd/mm/yyyy.

However I think the issue is, that there are multiple consultants with the same start and end date, which I didn't take into account in my sample data.

I think, I solved it by putting the Consultant field into the expression, so it looks like this:

Table:

LOAD *,

  Date("StartDate" + IterNo() - 1) as Date,

  Month("StartDate" + IterNo() - 1) as Month,

  Year("StartDate" + IterNo() - 1) as Year,

  Date(MonthStart("StartDate" + IterNo() - 1), 'MMM-YYYY') as MonthYear

While "StartDate" + IterNo() - 1 <= "EndDate";

LOAD * INLINE [

    FTE, StartDate, EndDate, Consultant

    1, 1/1/2017, 31/5/2017, A

    2, 1/2/2017, 31/10/2017, B

    1, 1/2/2017, 31/10/2017, C

    1, 1/2/2017, 31/10/2017, D

    2, 1/3/2017, 30/9/2017, E

    1, 1/3/2017, 31/12/2017, F

];

Expression:

Sum(Aggr(Sum(DISTINCT FTE), Month, Consultant, "StartDate", "EndDate"))

Would that be the right way to go?

sunny_talwar

Looks good to me...

Anonymous
Not applicable
Author

Thanks for your help