Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to aggregate daily data to weekly level

Hi All,

I have a qvd file which has traffic for daily level basically it has the traffic for a single day for each hour for each location.

Now, i have to use this QVD and create another one such as this one should give me the total traffic for the week for all stores.( the whole traffic should be set to the first day of the week i.e. the first day of every week will give me the total for that week)

Attached is my QVW which i am working on, I am joining this one to Date QVD which may help us to aggregate it to week level and the date one has all the date fields required.

Any suggestions or ideas?

Thanks

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

load

date(weekstart(date#(DateId,'YYYYMMDD')),'YYYYMMDD') AS aggrWeekStart, // as a date into YYYYMMDD format

LocationNum as aggrLocationNum,

sum(NumVisits) as aggrNumVisits

Resident Traffic

Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;

load

NUM#(TEXT(date(weekstart(date#(DateId,'YYYYMMDD')),'YYYYMMDD'))) AS aggrWeekStart, // as a NUMBER

LocationNum as aggrLocationNum,

sum(NumVisits) as aggrNumVisits

Resident Traffic

Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;

View solution in original post

3 Replies
whiteline
Master II
Master II

Hi.

Use group by:

LOAD

     LocationId,

     WeekName(DateId) as DateId,

     Sum(NumVisits) as TotalNumVisits

Resdient Traffic

Group by LocationId, WeekName(DateId);

Is there any reason that you load DateId so strangely? So that the text representation shows 20120129 but the numeric date corresponds to 1/14/56987.

Clever_Anjos
Employee
Employee

load

weekstart(date#(DateId,'YYYYMMDD')) AS aggrWeekStart,

LocationNum as aggrLocationNum,

sum(NumVisits) as aggrNumVisits

Resident Traffic

Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;

Or this if you don´t need it by location

load

weekstart(date#(DateId,'YYYYMMDD')) AS aggrWeekStart,

sum(NumVisits) as aggrNumVisits

Resident Traffic

Group by weekstart(date#(DateId,'YYYYMMDD'));

Clever_Anjos
Employee
Employee

load

date(weekstart(date#(DateId,'YYYYMMDD')),'YYYYMMDD') AS aggrWeekStart, // as a date into YYYYMMDD format

LocationNum as aggrLocationNum,

sum(NumVisits) as aggrNumVisits

Resident Traffic

Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;

load

NUM#(TEXT(date(weekstart(date#(DateId,'YYYYMMDD')),'YYYYMMDD'))) AS aggrWeekStart, // as a NUMBER

LocationNum as aggrLocationNum,

sum(NumVisits) as aggrNumVisits

Resident Traffic

Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;