Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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
Employee
Employee

Re: Re: how to aggregate daily data to weekly level

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
Honored Contributor II

Re: how to aggregate daily data to weekly level

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.

Employee
Employee

Re: how to aggregate daily data to weekly level

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'));

Employee
Employee

Re: Re: how to aggregate daily data to weekly level

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