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.
