What function do I use to group individual days into weeks?

[#1]

Hi,

I am  new to QlikView.   I am working with a file where I have multiple account values for each day of the calendar year..  I am using the average function in my expression to get the average value of an account for each day.  I would now like to group the days (a dimension) into calendar weeks so I can see the weekly average account value for each calendar week.

Please let me know if what function or code I must add to the (Date) dimension to group it into calendar weeks.

Thank you for your help with my question.

[Nicole-Smith]

week(Date) will return an integer relating to the number of the week of a year

There is also weekstart(Date) and weekend(date) if you want it in a date format.

[#3]

Yes. Create a seperate Calendar Table using the Date fields (say Dt) from your current Master Table (say MasterData) as under:

Calendar:
LOAD Dt,

          Year(Dt) as Year,

          'Q' & ceil(Month(Dt)/3) as Quarter,

          Month(Dt) as Month

           'Wk' & Week(Dt) as WeekNo,

          Weekstart(Dt) as WeekStartDate,

          Weekend(Dt) as WeekEndDate

RESIDENT MasterData;

By creating this seperate Date Dimension Table called Calendar, you would be able to maintain the data at the granular level of Dt at the same time able to see this data grouped at Year, Quarter, Month or Week levels using Avg/Sum group by functions.

[#4]

Hello Vishal,

Thank you for your help.  The Calendar Table seems like a viable solution to what I want to accomplish but when I paste the code into my file the reload errors out.  Is there additional coding needed to create the Calendar Table?  Please let me know if there is additional coding needed to make this work or if you notice a bug in the code. 

FYI.  The field I would be loading is Date.  In script I have replaced each instance of Dt with Date.  Is this the correct way to proceed in coding the Calendar Table?

Thank you for your suggestion and help.

Bill

[suresh_rawat]

Hi William,

For this you have to take calendar master.

From that you can find year, month, week, day,quarter, half year.

Just map your date fields with calendar master table fields by using Qualify.

Eg,

Qualify year, month,week,day;

Calendar Master:

Date,

year,

month,

.

.
;

Qualify year, month,week,day

Data_Table

Date,

Year as year,

Month as month,

Week as week,

.

.

;

By this you can use week as your dimension for getting calendar week wise data.

Hope this will help to resolve you problem.

Regards

Suresh Rawat