Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
How do i exclude 2nd and 4th saturday and all sundays in month??
Thanks& Regards,
Kshitija
HI,
Have a look at the link.
Regards,
Kaushik Solanki
May be create flags for these dates in your script.:
Table:
LOAD Date,
WeekDay(Date) as WeekDay,
If(WeekDay(Date) = 'Sun' or (WeekDay(Date) = 'Sat' and Match(Div(Day(Date), 7), 1, 3)), 1, 0) as Exclude_Flag;
LOAD Date(MakeDate(2016, 1, 1) + RecNo() - 1) as Date
AutoGenerate 366;
Hi Sunny,
To be more specific i have open date and close date and calculated TAT( ie.closedate-opendate) , TAT contains all saturdays and sundays, so i want to exclude 2nd and 4th saturdays and all sundays from thoes.
Thanks&Regards,
Kshitija
Add Flags to your Datefield or preferably in MasterCalendar as below
Dates = is your datefield
if(NUM(Dates) = makedate(YEar(Dates),NUM(Month(Dates)),1+7*2)-(NUM(WeekDay(MonthStart(Dates),6))+1),'2ndSat',
if(NUM(Dates) = makedate(YEar(Dates),NUM(Month(Dates)),1+7*4)-(NUM(WeekDay(MonthStart(Dates),6))+1),'4thSat')) as SaturdayFlag
adding the same flags to an Intervalmatch table should do it ! your thoughts stalwar1
I feel that could work but I guess the OP is looking to do it using NetWorkDays or Subtraction of two dates which is more complicated stuff. I have seen it done before, but would require some time and effort to get it working
Can you share sample data?
Hi Vineeth ,
I have attached sample data , i want to calculate the TAT of opening and closing date and then exclude 2nd and 4th Saturday and all Sundays.
Thanks!
Had to write a module to get this result in Qlikview,
I know you are using Qliksense but you can create a QVD using a personal edition and load that to your Qliksense app as a workaround until an someone has a better solution.
LOGIC: use networkdays to exclude all saturdays & sundays between the period and then add the number of 1st or 3rd saturdays falling in the period
MODULE:
Press CTRL + M and paste this code
Function AdjustSaturdays(StartDate, EndDate)
Days = 0
For Dates = StartDate To EndDate
'' Check for Weeknumber to identify saturday of the month
IF (Day(Dates)/7)<=1 THEN
SatNumber = 1
ELSEIF (Day(Dates)/7)>1 and (Day(Dates)/7)<=2 THEN
SatNumber = 2
ELSEIF (Day(Dates)/7)>2 and (Day(Dates)/7)<=3 THEN
SatNumber = 3
ELSEIF (Day(Dates)/7)>3 and (Day(Dates)/7)<=4 THEN
SatNumber = 4
ELSEIF (Day(Dates)/7)>4 and (Day(Dates)/7)<=5 THEN
SatNumber = 5
ELSEIF (Day(Dates)/7)>5 and (Day(Dates)/7)<=6 THEN
SatNumber = 6
END IF
If WeekDay(Dates) = 7 and (SatNumber = 1 or SatNumber = 3) THEN
Days = Days + 1
End If
Next
AdjustSaturdays = Days
End Function
IN YOUR LOAD SCRIPT:
TATDAYS:
LOAD
Dates,
Stop,
NetworkDays,
SaturdaysToAdd,
NetworkDays + SaturdaysToAdd as TATDays;
LOAD Dates,
Stop,
NetWorkDays(Dates,Stop) as NetworkDays,
AdjustSaturdays(NUM(Dates),NUM(Stop)) as SaturdaysToAdd
FROM
(ooxml, embedded labels, table is Sheet1);