8 Replies Latest reply: Jun 22, 2017 8:18 AM by Thomas Ã˜degaard

# 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:

 FTE StartDate EndDate 1 01/01/2017 31/05/2017 2 01/02/2017 31/10/2017 2 01/03/2017 30/09/2017 1 01/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

 Month FTE Jan 1 Feb 3 Mar 6 Apr 6 May 6 Jun 5 Jul 5 Aug 5 Sep 5 Oct 3 Nov 1 Dec 1
 Month FTE Jan 1 Feb 3 Mar 6 Apr 6 May 6 Jun 5 Jul 5 Aug 5 Sep 5 Oct 3 Nov 1 Dec 1

Sample data is attached

Br,

Thomas

• ###### Re: Number of FTE's remaining

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))

• ###### Re: Number of FTE's remaining

Maybe have a look at the IntervalMatch() function IntervalMatch

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

• ###### Re: Number of FTE's remaining

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.

• ###### Re: Number of FTE's remaining

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.

• ###### Re: Number of FTE's remaining

How does your date field look like?

• ###### Re: Number of FTE's remaining

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?

• ###### Re: Number of FTE's remaining

Looks good to me...

• ###### Re: Number of FTE's remaining

Thanks for your help