inweek with offset date

Mon, 26 Aug 2013 18:59:07 GMT

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.

Re: inweek with offset date

whiteline — Tue, 27 Aug 2013 07:41:49 GMT

Hi.

It seems that braсkets are missed in the last line: Num(WeekStartDay)

topic Re: inweek with offset date in QlikView

inweek with offset date

Re: inweek with offset date