topic Re: aggregating daily to weekly data in QlikView

aggregating daily to weekly data

Tue, 24 Sep 2013 14:57:18 GMT

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

Re: aggregating daily to weekly data

datanibbler — Tue, 24 Sep 2013 15:28:19 GMT

Hi Mike,

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

HTH

Best regards,

DataNibbler

Re: aggregating daily to weekly data

Tue, 24 Sep 2013 15:35:11 GMT

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

Re: aggregating daily to weekly data

v_iyyappan — Tue, 24 Sep 2013 17:45:57 GMT

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)