a general calendar to count day(s)

[laujerry]

hi all,

I have to count how many (sales) working days are in a certain month at the beginning of month

It will be calculated by subtracting # of Sunday from total # of days in a month (ignore other holidays here)

if the day count can be taken at the end of month, i can simply assume # of distinct sales date as the working day count

but to take this value at the beginning of month, this will require me to create such a table like:

GeneralCalendar(GeneralCalendarDate, GeneralCalendarYear, GeneralCalendarMonth, GeneralCalendarDay, GeneralCalendarWeekday)

// expression in chart with dimension in month

Sum(

   {<

      GeneralCalendarWeekday-={'Sunday'}

   >}

   GeneralCalendarDate

)

so, is there any better way to count # of days other than particularly creating a table for this purpose?

thank you!

[er_mohit]

i think you have to learn about this function for more detail either go to refrence manual or in help of qlikview

or search in community

see this

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9 

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8 

[laujerry]

thank you, i have not seen this function before

but this function does not count saturday...

by some searching, i see ppl suggest writing macro scripts to solve

http://community.qlik.com/thread/71618

so there is no other simpler way to counter with this?

[marcus_sommer]

A possibility is to use a master calendar in load script whereby every day unless sunday is 1 and sunday is 0 and use a simple sum over this field in GUI.

- Marcus

[laujerry]

yep it is possible but this is similar to the idea of creating a general calendar as mentioned in my question

[laujerry]

so finally i make a "so-called" calculation to get the num of Saturday

u will have at least 4 Saturday for each month (at least 28 days for a month)

and by drawing calendars, it can be easily found that

extra Saturday(s) is "granted" only when 1st of day is on Thu/Fri/Sat

where # of extra days depends on # of days in the month

i can make the expression by just several if statements

NetWorkDays(

   MonthStart(vDate),MonthEnd(vDate)

)

// Mon=0,Tue=1,Wed=2,Thu=3,Fri=4,Sat=5,Sun=6

+

if(Num(WeekDay(MonthStart(vDate)))=3 and Round(MonthEnd(vDate)-MonthStart(vDate))=31,1+4,

   if(Num(WeekDay(MonthStart(vDate)))=4 and Round(MonthEnd(vDate)-MonthStart(vDate))>=30,2+4,

      if(Num(WeekDay(MonthStart(vDate)))=5 and Round(MonthEnd(vDate)-MonthStart(vDate))>=29,3+4,

         4

      )

   )

)