Number of FTE's remaining

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

Maybe have a look at the IntervalMatch() function IntervalMatch

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

[Anonymous]

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]

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]

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]

Thanks for your help