topic Re: working Days Logic in QlikView

working Days Logic

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

Hi Guys,

I have attached one QVD data, i this you will see Plant as WERKS, PRCTR as Profit Centre, MATKL of Material Group and OffDate as Date of Holiday.

You will see the only entries of holidays. if the entry is not There against any possible combination than it means All day working.

Possible Combination are below

Load * Inline

[PRCTR, MATKL, WERKS

2001, 14001, 1001

2002, 14002, 1001

2003, 14003, 1001

2004, 14004, 1001

2005, 14009, 1001

2001, 14010, 1001

2011, 14007, 1001

2012, 14005, 1001

2013, 14006, 1001

2101, 14011, 1001

2102, 14012, 1001

2103, 14013, 1001

2104, 14014, 1001

2105, 14018, 1001

2112, 14015, 1001

2290, 14101, 1001

2291, 14101, 1001

2292, 14101, 1001

2293, 14101, 1001

3001, 14001, 1002

5001, 14001, 1003

6002, 14002, 1004

7001, 14001, 1005

7002, 14002, 1005];

Now my requirement is i want to calculate the No of Working Days of the Month and No of Current Working Days.

e.g.

PRCTR, MATKL, WERKS, OffDate

2001, 14001, 1001, 12.06.2016

that above entry means with above combination No of working Days would be 29 current as well as for the month.

Current Working Days Definition is = if there are four holidays and and one holiday is on 30.06.2016 and you are in 29.06.2016 than current working days would be 29 total days - 3 holidays = 26

and Total working Days = 30 days - 4 holidays = 26

Hope m clear thanks in advance

Re: working Days Logic

Thu, 30 Jun 2016 17:48:16 GMT

Please try below script:

TempOffDays:

LOAD DISTINCT

OFF_DT,

MonthYear(OFF_DT) AS MonthYear_Name,

IF(OFF_DT > Today(),0,1) AS CurrentDaysFlag

FROM Stage1_ZCALENDER.qvd (qvd);

OffDays:

LOAD

MonthYear_Name

Count(OFF_DT) as TotalOffDays,

Sum(CurrentDaysFlag) as CurrentOffDays

Resident TempOffDays;

DROP Table TempOffDays;

You can join "OffDays" with you calendar and calculate the Total Working days or Current Working days.

Re: working Days Logic

abhaysingh — Fri, 01 Jul 2016 03:43:06 GMT

JO dathu,

thanks for revert,

TempOffDays:

LOAD DISTINCT

OFF_DT,

MonthYear(OFF_DT) AS MonthYear_Name,

IF(OFF_DT > Today(),0,1) AS CurrentDaysFlag

FROM Stage1_ZCALENDER.qvd (qvd);

this script giving error, What monthyear function will do????bcoz of it getting error..

Re: working Days Logic

abhaysingh — Fri, 01 Jul 2016 03:46:48 GMT

with the below script we must have to use group by function right?

OffDays:

LOAD

MonthYear_Name

Count(OFF_DT) as TotalOffDays,

Sum(CurrentDaysFlag) as CurrentOffDays

Resident TempOffDays;