How to Exclude Weekends and Public Holidays

[Anonymous]

Hi Guys

I have attached a sample of the Excel file I've loaded onto Qlikview.

I used a CrossTable to load the above. There is a Consultant field, a LeaveCode field that has for example (AA- Approved Annual Leave; PH - Public Holiday) and then calendar dates for 2018 that appear across columns in which the column name is called CalDate.

There are 12 Public holidays and I've highlighted them in green as per the example. How do I exclude these in counting the leave days taken by employees?

Also, how can I exclude weekends when taking into account leave days taken that were taken either prior to a weekend or after a weekend?

Lastly, what formula/syntax would I use to show MTD leave taken as currently an employees leave balance would show that leave was taken for all the 12 public holidays listed on my excel sheet.

I've attached the actual leave sheet as well (Calendar Leave Template TEST)

[shiveshsingh]

May be this?

[shiveshsingh]

For excluding the declined leaves, put one more filter to exclude DA.

IN dimension, make one more dimension as Week which will let you know week wise leaves for consulatants

[Anonymous]

Hi Shivesh

Thank you for your assistance. I will have a look and let you know if it resolves my issue.

[shiveshsingh]

Yes, please check and let me know