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

      ID,

      WeekStartDay,

      Date,

      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.

       

       

       

      @