topic Re: Re: Extract 5th working day of Month. in QlikView

Extract 5th working day of Month.

Fri, 18 Oct 2013 06:40:23 GMT

hi,

I have a condition which becomes true when toady is fift working of day of month. how to extract fifth working day of month.please help.

Regards,

Piyush Jain

Re: Extract 5th working day of Month.

saumyashah90 — Fri, 18 Oct 2013 07:02:56 GMT

hey piyush look at thiscompare today with

this

Date(MonthStart(Today(),0)+4,'DD-MM-YYYY')

Re: Extract 5th working day of Month.

tresB — Fri, 18 Oct 2013 07:18:01 GMT

Try this:

=FirstSortedValue(DISTINCT date,NetWorkDays(MonthStart(date),date),5)

PFA

Re: Extract 5th working day of Month.

saumyashah90 — Fri, 18 Oct 2013 07:18:18 GMT

try this

if(Weekday(Date(MonthStart(Today(),0)+4,'DD-MM-YYYY'))='Sat',Date(MonthStart(Today(),0)+6,'DD-MM-YYYY'),

if(Weekday(Date(MonthStart(Today(),0)+4,DD-MM-YYYY'))='Sun',Date(MonthStart(Today(),0)+5,'DD-MM-YYYY')))

Re: Extract 5th working day of Month.

MayilVahanan — Fri, 18 Oct 2013 07:21:49 GMT

HI
May be try like this also

If(Date = MonthStart(Date),Pick(Match(WeekDay(Date),'Sat','Sun')+1,Date(MonthStart(Date,0)+7,'DD-MM-YYYY'),Date(MonthStart(Date,0)+6,'DD-MM-YYYY'),Date(MonthStart(Date,0)+5,'DD-MM-YYYY')))

Re: Extract 5th working day of Month.

Fri, 18 Oct 2013 07:22:57 GMT

Hi Piyush,

Here is the script you needed

DAY:
LOAD Date(Today()+RecNo()) as Date
AutoGenerate 365;

Left Join(DAY) LOAD
Date,
Month(Date) as Month,
WeekDay(Date) as Day,
If(Date=LastWorkDate(MonthStart(Date),5),1,0) as FifthWorkingDay
Resident DAY;

Regards

GowriShankar

Re: Extract 5th working day of Month.

Fri, 18 Oct 2013 07:33:31 GMT

Hi Mayil,

Expression is correct but not showing date.

Regards,

piyush

Re: Extract 5th working day of Month.

jagan — Fri, 18 Oct 2013 07:36:12 GMT

Hi,

Try Lastworkdate() like this

lastworkdate (MonthStart(DateDimensionName), 5))

Hope this helps you.

Regards,

Jagan.

Re: Re: Extract 5th working day of Month.

saumyashah90 — Fri, 18 Oct 2013 07:38:36 GMT

Re: Extract 5th working day of Month.

MayilVahanan — Fri, 18 Oct 2013 07:41:31 GMT

Try like this

lastworkdate (MonthStart(DateDimensionName), 5))

Hope tat helps

Re: Extract 5th working day of Month.

Fri, 18 Oct 2013 07:48:54 GMT

Hi,

This is good but if saturday and sunday lies between 1 to 5th and saturday on fifth day or sunday on fifth day then fith working day shift to 6 or 7th of that month.

Regards,

piyush

Re: Extract 5th working day of Month.

MayilVahanan — Fri, 18 Oct 2013 07:54:34 GMT

Can you share sample file?

Re: Extract 5th working day of Month.

jagan — Fri, 18 Oct 2013 07:54:36 GMT

Hi Piyush,

Try like this

=MakeDate(Year(DateDimension), Month(DimensionName), 5)

Hope this helps you.

Regards,

Jagan.

Re: Extract 5th working day of Month.

MayilVahanan — Fri, 18 Oct 2013 08:05:52 GMT

Hi
Try like this

Let vEnd = Num(Today(1));

Let vStart = Num(YearStart($(vEnd),-4)) - 1;

Generate:

LOAD Date($(vStart) + IterNo()) AS Date , num((rand() * 100) + 1000, '##0') as Sales

AutoGenerate 1

While ($(vStart) + IterNo()) <= $(vEnd);

Tablename:

Load *, If(Date = MonthStart(Date),Pick(Match(WeekDay(Date),'Sat','Sun')+1,Date(MonthStart(Date,0)+6,'DD-MM-YYYY'),Date(MonthStart(Date,0)+6,'DD-MM-YYYY'),Date(MonthStart(Date,0)+5,'DD-MM-YYYY'))) As FifthWorkingDay;

LOAD Year(Date) AS Year,Date,Day(Date) AS Day,Num(Date) as DateNUm ,WeekDay(Date) as Week,MonthName(Date) AS MonthYear

Resident Generate ;

Re: Re: Extract 5th working day of Month.

saumyashah90 — Fri, 18 Oct 2013 08:16:22 GMT

this will solve your question

Re: Extract 5th working day of Month.

Thu, 24 Oct 2013 12:25:42 GMT

Hi,

I have to extract 5 day from formula =networkdays(Monthstart( today() ),Monthend( today() )).

Regards,

Piyush Jain