Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

toedegaard
New Contributor II

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

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

Capture.PNG

8 Replies

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

Capture.PNG

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.

toedegaard
New Contributor II

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.

toedegaard
New Contributor II

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?

toedegaard
New Contributor II

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

toedegaard
New Contributor II

Re: Number of FTE's remaining

Thanks for your help

Community Browser