topic Re: Count resources available before a date in QlikView

Count resources available before a date

Tue, 03 Dec 2013 14:28:37 GMT

Hi,

I am currently tracking supply and demand within an app.

I am looking to create a chart which shows the demand for skills as well as the currently supply of employees we have before a demand start date.

I have the chart which shows the current current employees available now vs the amount of employees wanted with these two expressions:

Available skills
Count({$<[Billable_Status] = {'Non-Billable}>}[Billable_Status]

Resources wanted

Sum({1}[Resources_Wanted])

The dimension is [Employee_Primary_Skill]

What I'd like to do now is count the employees which become available before a demand start date, I have two dates for this [Employee_Allocation_End] and [Demand_Billable_Start]. The dimension on the chart would no longer be shown by skill but [Demand ID] but the employee would still have to have the same primary skill the demand needs.

Thanks in advance, sorry if I've made this seem confusing. I'm a bit baffled myself!

Re: Count resources available before a date

Tue, 03 Dec 2013 15:03:10 GMT

Can you upload a sample file?

Re: Count resources available before a date

Wed, 04 Dec 2013 12:49:12 GMT

I've managed to attach my sample data. Do you need the qlikview page I'm working on too?

Re: Count resources available before a date

Wed, 04 Dec 2013 16:09:53 GMT

Yes that would be good. All I see in your sample file is Client_ID and Client Name. I don't see anything with skills or resources.

Re: Count resources available before a date

Wed, 04 Dec 2013 16:14:50 GMT

I think you need to tab across to the other sheets in the bottom left of excel.

Re: Count resources available before a date

Wed, 04 Dec 2013 16:20:49 GMT

Ah, yes. The power of observation

Re: Count resources available before a date

Wed, 04 Dec 2013 16:33:06 GMT

Ok, see if I am on the same page as you for what you have so far. I'm not sure I fully understand what you are looking for.

Re: Count resources available before a date

Wed, 04 Dec 2013 16:55:06 GMT

That is similar to what I want to achieve.

I think in my script I renamed the Primary_Skill from the demand to match the EE_Primary_Skill to get the totals.

From there I want to be able to have another expression which will count the number of resources that will become available before the demand start date. So where Allocation_End is before the Demand_Billable_Start_Date. And it still has the same skill.

So I would effectively count

Employees who are non-billable and have the same primary skill as the one in the demand

Employees who are billable and have the same primary skill as the demand, but whose allocation_end is before the Demand_billable_start