1 Reply Latest reply: Aug 27, 2013 3:41 AM by whiteline _ RSS

    inweek with offset date

      I have a set of data with 2 tables, one with IDs, starting days of the week and another with dates. I need to find the number of unique dates that are in the week starting with the day of a week for each ID. For instance, if one ID has a WeekStartDay of Monday, I want the weeks to be from Monday to Sunday. If another IDs WeekStartDay is Wednesday I want the weeks to be Wednesday to Tuesday. If a date occurs twice in the list I want to count only one.


      I was trying something like


      Load distinct




      WeekStart(Date, 0,Num(WeekStartDay) AS WeekStart,   // Weekstart using offset of weekday

      Count(Distinct     Inweek( Date,   WeekStart(Date,0,Num(WeekStartDay)   )  ) AS DaysInWeek  // count the number in the week.



      Group by ID, WeekStart(Date,0, NumWeekStartDay)




      No matter what I try I get an invalid expression. Am I on the right track or is there a better way? Sample data is attached.