Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

@

Tags (4)
1 Reply
whiteline
Not applicable

Re: inweek with offset date

Hi.

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