topic Re: count days from range!!! in QlikView

count days from range!!!

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

Hi Everyone,

Below are the working days from Monday to Friday and we are not considering Saturday and Sunday and other holidays

From here I want to calculate no of working days per month.

In March, total working days

1-3------>3

6-10---->5

13-17--->5

20-24--->5

27-31--->5

3+5+5+5+5=23 days

In KPI, I want to show no of working days for remaining months as well.

How do we accomplish this requirement.

Kindly suggest.

Re: count days from range!!!

PrashantSangle — Mon, 18 Sep 2017 10:46:19 GMT

try with straight table

dimension -> month

expression

->sum(subfield(Schedule week_new,'-',2)-subfield(Schedule week_new,'-',1))

Regards,

Re: count days from range!!!

tresB — Mon, 18 Sep 2017 10:48:33 GMT

Or may be just (without sum) this?

subfield(Schedule week_new,'-',2)-subfield(Schedule week_new,'-',1) +1

Re: count days from range!!!

PrashantSangle — Mon, 18 Sep 2017 10:55:46 GMT

Thanks tresesco for correction but I am not sure how to write logic for last value in Schedule week_new list box.

Regards,

Re: count days from range!!!

andrei_delta — Mon, 18 Sep 2017 10:56:58 GMT

hello,

why don't you calculate the no of Working Days in the script where you calculate the Schedule week_new field and in the UI you just use a simple sum aggregated by Month?

Regards,

Andrei

Re: count days from range!!!

antonybi — Mon, 18 Sep 2017 10:58:00 GMT

Thanks for quick response!!!

As above snapshot, it is clearly showing for Jan month 30-3(Jan30-Feb3)

Here, I want to split the dates Jan30 and Jan31 into Jan Month

and consider remaining days(Feb1, Feb2 and Feb3) into Feb Month.

is this possible ??

Re: count days from range!!!

Kushal_Chawda — Mon, 18 Sep 2017 11:49:27 GMT

another approach could be, create field in script

LOAD *,

fabs(Evaluate(Field))+1 as WorkingDays

FROM table;

Now you can write the expression

sum(WorkingDays)

Note:Field = schedule week_new

Re: count days from range!!!

omkarvamsi — Mon, 18 Sep 2017 13:57:34 GMT

you can use network days function and also we can get count of working days after excluding holidays too