topic Re: Date Difference Excluding weekends in QlikView

Date Difference Excluding weekends

Mon, 15 Sep 2014 13:08:49 GMT

Hi all

I want to be able to see how many working days are letf in a month, for example what I have below is;

But I want to remove the count for the weekend days or even just Sunday if possible.

Any ideas?

I want this as a caculated dimension in a pivot chart.

=MONTH_END-today()

Thanks

Aidan

Re: Date Difference Excluding weekends

MK_QSL — Mon, 15 Sep 2014 13:12:06 GMT

Including Weekends.

=Interval(MonthEnd(Today())-Today(),'dd')

Excluding Weekends.

=NetWorkDays(Today(),MonthEnd(Today()))

Re: Date Difference Excluding weekends

anbu1984 — Mon, 15 Sep 2014 13:12:43 GMT

=NetWorkDays(today(), Month_End)

Re: Date Difference Excluding weekends

Mon, 15 Sep 2014 13:20:51 GMT

Thansk you this seems to have worked for me, is their anyway I can discount any day that is Sunday on this?

Thanks again,

Aidan

Re: Date Difference Excluding weekends

marcus_sommer — Tue, 16 Sep 2014 01:47:21 GMT

Unfortunately networkdays included no possibility for saturday or sunday. If you needs this you need a more complex expression which additionally counted these day. Easier will be to use a special field within the master-calendar like:

if(num(weekday(date)) = 6, 0 ,1) as working_day

and the use a simply sum(working_day) within your chart.

- Marcus

Re: Date Difference Excluding weekends

sebastiandperei — Tue, 16 Sep 2014 03:01:34 GMT

What you mean that you need as calculated dimention?

You want to see like one row by every "non sunday" day? What you have in MONTH_END field?

Re: Date Difference Excluding weekends

Tue, 16 Sep 2014 07:05:11 GMT

So the below is what I have but I want this to only exclude Sunday and include Saturday

Excluding Weekends.

=NetWorkDays(Today(),MonthEnd(Today()))

Re: Date Difference Excluding weekends

MK_QSL — Tue, 16 Sep 2014 07:27:13 GMT

What is meant by Discount Sunday?

Please elaborate little more on this !

Re: Date Difference Excluding weekends

Tue, 16 Sep 2014 07:30:33 GMT

So I want to know how many working days are left in the month, so today being the 16th, we have 13 work days left including today but excluding the last 2 Sundays of the month.

Re: Date Difference Excluding weekends

MK_QSL — Tue, 16 Sep 2014 07:35:25 GMT

=CEIL(Interval(MonthEnd(Today())-Today(),'dd') - Div(MonthEnd(Today())-Today(),7) + (weekday(Today()) > WeekDay(MonthEnd(Today()))))

Re: Date Difference Excluding weekends

Tue, 16 Sep 2014 07:44:10 GMT

Thank you kindly, worked perfectly

Re: Date Difference Excluding weekends

sebastiandperei — Wed, 17 Sep 2014 03:29:39 GMT

Ok, you need only the number of days between 2 dates without the sundays. Try:

/*1*/ =NetWorkDays(Today(),MonthEnd(Today())) +

/*2*/ Div(Floor(MonthEnd(Today())-Today())+1,7)+

/*3*/ if(WeekDay(Today())>=WeekDay(MonthEnd(Today())+2)

/*4*/ and WeekDay(Today())<>6,1, 0)

/*1*/ Days Monday to Friday next

/*2*/ Complete following weeks always have one Sat each one

/*3*/ If weekday(today) is grater than weekday(monthend+2), it means that you have left a weekend plus the complete weeks

/*4*/ With this, you don't plus the Sundays

Sorry if my english is not good.

Re: Date Difference Excluding weekends

sebastiandperei — Wed, 17 Sep 2014 12:48:51 GMT

Wow Manish, while you are typing this solution, i was thinking about 2 hours in my solution (on Bottom)... Your one is very simple and "beautiful", but mine is ugly!!!! I don't want to correct your solution, the time between the answers is because im in Argentina.

Is amazing to keep learning from people like you!!! Thanks.