topic Re: Logic Help in QlikView

Logic Help

vaishali_k — Thu, 26 Jul 2018 10:34:56 GMT

Hi Community,

I have below data

Date

05/07/2018

06/07/2018

07/07/2018

08/07/2018

09/07/2018

10/07/2018

11/07/2018

12/07/2018

13/07/2018

14/07/2018

In this data 08/07/2018 is Sunday and 11/07/2018 is National holiday. I want new field which calculated like if date 05/07/2018 add date +5 and i will get Output is date 12/07/2018 that means in calculation leave sunday and holiday expected output show in below table

Date Output

05/07/2018 12/07/2018

06/07/2018 13/07/2018

07/07/2018 14/07/2018

08/07/2018 14/07/2018

09/07/2018 16/07/2018

10/07/2018 17/07/2018

11/07/2018 17/07/2018

Above logic is applied for Output = (Date+5) (Excluded Sunday and Holiday) how i will get this output please help me.

Thanks in Advance.

Re: Logic Help

m_woolf — Thu, 26 Jul 2018 17:42:54 GMT

Look at LastWorkDate

Re: Logic Help

vaishali_k — Fri, 27 Jul 2018 05:05:32 GMT

Explain in terms of above example will be very helpful for me.

Re: Logic Help

Anil_Babu_Samineni — Fri, 27 Jul 2018 13:52:08 GMT

How come 7 will be 14?

Re: Logic Help

BalaBhaskar_Qlik — Fri, 27 Jul 2018 17:16:18 GMT

May be, like this:

=NetWorkingDays(Date#('07/25/2018','MM/DD/YYYY'),Today(),$(=Concat(chr(39)&If(HOLIDAY='Y',Date)&Chr(39),',')))

Or Script ex:

MySales:

Load

Date(Date#(SalesDate, 'DD.MM.YYYY')) as SalesDate, Sales

Inline [

SalesDate, Sales

03.05.2018, 230

04.05.2018, 25

16.05.2018, 66

10.05.2018, 120

19.05.2018, 32

12.04.2018, 100

05.04.2018, 12

03.06.2018, 88

];

Sales:

Load *,

Month(SalesDate) as MyMonth,

MonthName(SalesDate) as MyMonthYear,

if(Year(SalesDate) = Year(Today()) and Month(SalesDate) = Month(Today()),

NetWorkDays(MonthStart(Today()),(Today()),$(vPublHoliday)),NetWorkDays(MonthStart(SalesDate),MonthEnd(SalesDate),

$(vPublHoliday))) as MyWorkingDays

Resident MySales;

Re: Logic Help

vaishali_k — Tue, 31 Jul 2018 09:51:51 GMT

As i mentioned earlier suppose order is placed on 7th July 2018 (On Saturday) i.e 8th July is Sunday and 11th July is holiday as per given data, (So skip Sunday and holiday).

so as per the logic (day+5) leaving Sunday and holiday 7th July's output is 14th July. Hope this is clear.