and so on..
I can do grouping by month and by weeks by adding a seprate month and week coulmn using format function, but by dong this way a week can be split into two months
What I need is it to group days by 7 days period. So 1-7 would be week1, 8-14 be week2 and so on. and the last week period could vary between 0-3 depending on which month it is. It does not matter as to wether 1st day of the week is a sun or mon.
I hope I have made my requirement clear and hope some expert could guide me into the correct way of doing it.
if the week number itself has no implicit meaning (ie its not ited to you accounting calendar) you should be able derive a week number using the datepart() function, that weeknumber and a year should uniquely idsentify any 7 day period.
so you could use a user written function to generate your week (year number say YYYY-WW) which you could then use in your further processing.