Aggregate Daily Data to Weekly

[#1]

Hi Gurus,

We have a data model built which holds daily transactions data. We now have a requirement to build a data model which will have data at weekly level.

What are the possible approaches to achieve this?

Thank you.

Regards,

Pramod K

[tresesco · Accepted solution]

May be just creating an additional field like:

Table1:

Load

          Date,

          Week(Date) as WeekNum,

          WeekName(Date) as WeekName

          Amount

From <>;  

Now report against WeekNum/WeekName fields in the front-end.

Or, if you want to remove the date-detailing you might want to aggregate the data at the script as well, like:

Load

          WeekNum,

          Sum(Amount) as AggrAmount

Resident Table1 Group By WeekNum;

Drop Table1;   //drop to reduce the file size

But if the size is not a concern, better don't kill the detailed scope by aggregating at at the script.