topic Re: Holiday Working question in QlikView

Holiday Working question

abhaysingh — Thu, 11 Feb 2016 06:45:52 GMT

I have one table in which Total holidays dates are present,

i want total working days to count which is Count(Total Days of Month) - Count(Holidays)

getting some glitch in output pls suggest.

pls find the attached sample data for holidays

Re: Holiday Working question

MK_QSL — Thu, 11 Feb 2016 06:48:12 GMT

You can use Networkdays function..

function:networkday

Re: Holiday Working question

abhaysingh — Thu, 11 Feb 2016 06:55:18 GMT

sorry not understood

Re: Holiday Working question

sasikanth — Thu, 11 Feb 2016 07:00:41 GMT

Try this

Networkdays(startdate,enddate,holidays)

*there is no transaction date column other than holidays in you attachment

Re: Holiday Working question

MK_QSL — Thu, 11 Feb 2016 07:04:55 GMT

To understand it.. click on the link which I have provided..

Re: Holiday Working question

abhaysingh — Thu, 11 Feb 2016 07:07:09 GMT

i dont have start date and end date, in this so in that case what to do?

Re: Holiday Working question

MK_QSL — Thu, 11 Feb 2016 07:08:39 GMT

Provide proper data.... Along with the result you are looking for..

Both in excel file..

Re: Holiday Working question

abhaysingh — Thu, 11 Feb 2016 07:10:46 GMT

hi manish i have this file only, u can take continuous dates calendar.

Re: Holiday Working question

deepakqlikview_123 — Thu, 11 Feb 2016 07:12:05 GMT

Hi ,

do you have total count of all holidays or only default holidays are there for sunday.Please specify your requirement specifically.

Thanks

Re: Holiday Working question

sasikanth — Thu, 11 Feb 2016 07:15:44 GMT

please check the attachment

Re: Holiday Working question

MK_QSL — Thu, 11 Feb 2016 07:19:13 GMT

I want to understand what result you are looking for?

Don't want so many replies for a simple question...

Re: Holiday Working question

abhaysingh — Thu, 11 Feb 2016 07:28:40 GMT

Pls find the attachment of sample working and output expected

Re: Holiday Working question

abhaysingh — Thu, 11 Feb 2016 07:30:22 GMT

hi Deepak,

i have holidays including Sunday and Holidays. only require to do let say jan has 31 days and holidays are 7 in any plant

so in that plant ouput expexted is 31-7 = 24

hope i m clr now

Re: Holiday Working question

asgardd2 — Thu, 11 Feb 2016 07:38:55 GMT

I hope, this solution is correct.

Application in attachment.

Re: Holiday Working question

jagan — Thu, 11 Feb 2016 07:49:34 GMT

Hi,

Try this expression

=MonthEnd(Min({< Flag = {'Calendar'}>} [Calendar Date])) - MonthStart(Min({< Flag = {'Calendar'}>} [Calendar Date]))

For Total Month Days

Hope this helps you.

Regards,

jagan.

Re: Holiday Working question

abhaysingh — Thu, 11 Feb 2016 08:07:36 GMT

HI Jagan,

if i am in feb now and till now only 11 total days are there and one holiday is applicable, so for that output shud come 10

but i am getting 29(total days on feb)- 4(total holidays of feb). how to include this?

Re: Holiday Working question

jagan — Thu, 11 Feb 2016 08:18:43 GMT

Hi,

Try like this

=If(MonthName(Min({< Flag = {'Calendar'}>} [Calendar Date])) <> MonthName(Today()),

MonthEnd(Min({< Flag = {'Calendar'}>} [Calendar Date])),

Min({< Flag = {'Calendar'}>} [Calendar Date]))

MonthStart(Min({< Flag = {'Calendar'}>} [Calendar Date]))

Re: Holiday Working question

abhaysingh — Thu, 11 Feb 2016 08:38:16 GMT

thanks for revert, but its

giving me wrong output, till now feb has 11 days and there is 1 leave so it shud give 10 working days, it is giving me 6.

and one more thing want to add, i am looking all this as per fiscal year

Re: Holiday Working question

abhaysingh — Thu, 11 Feb 2016 09:02:47 GMT

thanks for ur help,but seems not fulfill

In Jan there are 31 days and in ur code its 30 and if i m in feb, than it is taking full Month days i.e 29 but currently in feb there are 11 days, so before 11 days only one holiday than count shud be 11-1= 10.

pls suggest

Re: Holiday Working question

jagan — Thu, 11 Feb 2016 09:19:31 GMT

HI,

Try like this

=If(MonthStart(Min({< Flag = {'Calendar'}>} [Calendar Date])) >= MonthStart(Today()), Today() + 1,

MonthEnd(Min({< Flag = {'Calendar'}>} [Calendar Date])))

MonthStart(Min({< Flag = {'Calendar'}>} [Calendar Date]))

Regards,

Jagan.