2021 Calendar in Qliksense

[KishoreSuriF]

Hi Team,

We have 2021 Calendar in Excel which shows the details of EMPLOYEE Leaves on which date in a pivot table

So, we wants to recreate the same calendar with EMPLOYEE occupancy % ( No.of available on that day)

Ex: On 25 Oct 2021, 2 Employees on leave ,output should be show 80% 

On 26 Oct 3021, all are available, then it should be 100%

Please help me with your suggestions.

[anthonyj]

Hi @KishoreSuriF ,

We can create a master calendar date table that has every date between your set ranges. You can make it last 12 months or 2 years up to which ever date you choose. This column you'll use as a dimension.

This table can be associated to your employee leave dates table linked by the dates. Now, it appears from your example that a full count of employees is 10 and if that's always the case your denominator is simple. If it's more fluid then there's more work to do around how many staff you actually have employed on any given date.

Sticking with the solid 10 for the moment, I've given an example of how this can be modelled to give a percentage on any given day.

// This step will loop through and create a record for every day between the two dates.

// This can be made dynamic using min and max functions if you want it to be read from another source.

Calendar:
Load
date(StartDate + (IterNo()-1)) as [Calendar]
while StartDate + (IterNo()-1) < EndDate
;
load * Inline [
StartDate, EndDate
01/07/2021, 01/08/2021
];

Leave:
load * Inline [
Calendar, Name
05/07/2021, Sally
20/07/2021, Sally
21/07/2021, Sally
05/07/2021, John
20/07/2021, Frank
];

Now in a table you can add "Calendar" and in the measure add in "1-(Count(Name)/10)" resulting the percentage of occupancy for the day.

I hope this helps.

Thanks

Anthony

[KishoreSuriF]

Thanks Anthony, we'll try with above and check for the result.