aggregating daily to weekly data

[#1]

Good Afternoon all,

I have a table at present that looks like the below:

Date	Total
01/01/2013	4
02/01/2013	2
03/01/2013	6
04/01/2013	8
05/01/2013	2
06/01/2013	1
07/01/2013	2
08/01/2013	8
09/01/2013	8
10/01/2013	10
11/01/2013	4
12/01/2013	8
13/01/2013	7
14/01/2013	8

I am looking to use set analysis to group this into weeks so the table looks like this:

Date	Total
07/01/2013	25
14/01/2013	53

I would be very grateful for any help that anyone can provide

Many thanks

Mike

[datanibbler]

Hi Mike,

just switch the dimension and use the SUM() fct.

HTH

Best regards,

DataNibbler

[#3]

I would suggest a minor change to your script.

Add:  Week(Date) as Week

or:     Week(Date)&'-'&Year(Date)     as     WeekYear

to the table that also holds the date.

Having done that you're able to use SUM(Total) to find the sumarised total you're looking for

[v_iyyappan · Accepted solution]

Hi,

Script:

LOAD

    *,

    Week(Date(Date#(Date1,'DD/M/YYYY'))) AS Week,

    WeekName(Date(Date#(Date1,'DD/M/YYYY'))) AS WeekYear;

LOAD * INLINE

[Date1, TotalData

    1/01/2013,4

    2/01/2013,2

    3/01/2013,6

    4/01/2013,8

    5/01/2013,2

    6/01/2013,1

    7/01/2013,2

    8/01/2013,8

    9/01/2013,8

    10/01/2013,10

    11/01/2013,4

    12/01/2013,8

    13/01/2013,7

    14/01/2013,8

];

Use expression like

Dimension WeekYear and expression = Sum(TotalData)