topic Re: How to Exclude Weekends and Public Holidays in QlikView

How to Exclude Weekends and Public Holidays

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: How to Exclude Weekends and Public Holidays

shiveshsingh — Mon, 21 May 2018 10:45:17 GMT

May be this?

Re: How to Exclude Weekends and Public Holidays

shiveshsingh — Mon, 21 May 2018 10:59:10 GMT

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

Re: How to Exclude Weekends and Public Holidays

Anonymous — Mon, 21 May 2018 11:50:23 GMT

Hi Shivesh

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

Re: How to Exclude Weekends and Public Holidays

shiveshsingh — Tue, 22 May 2018 04:35:16 GMT

Yes, please check and let me know