AndyC1000
asked on
Can Date functions be used to convert Daily Dataset to Weekly?
Dear all,
I have a dataset in the following format containing 12 years of daily data for each of the 40 district areas. i.e. 1/1/2002 will contain 40 records for each of the 40 districts. The example data below is for two districts only for two dates thought more of the dataset would make the question too long.
Date District Loss Hectare Count
1/01/2002 1 10 10 10
1/02/2002 2 20 20 20
2/02/2002 1 11 11 11
2/02/2002 2 21 21 21
Is it possible to use the Date function to aggregate the data to weekly?
See below output example, record 1 aggregates all values from 1/01/2002 to 7/01/2002 for the selected district area (value of district field). The next data record aggregates all values from 8/01/2002 to 14/01/2002 for the selected district area (value of district field) and so on.
Is there a way to accompish this I haven't been able to find a solution.
I've tried pivot tables in MS Excel but having issues with the district field, haven't been able to get the district into a column like below. The feature to group days is great.
i.e.
Date District TotalLoss TotalHectare TotalCount
1/01/2002 1 50 100 150
14/01/2002 1 50 100 150
Thanks
I have a dataset in the following format containing 12 years of daily data for each of the 40 district areas. i.e. 1/1/2002 will contain 40 records for each of the 40 districts. The example data below is for two districts only for two dates thought more of the dataset would make the question too long.
Date District Loss Hectare Count
1/01/2002 1 10 10 10
1/02/2002 2 20 20 20
2/02/2002 1 11 11 11
2/02/2002 2 21 21 21
Is it possible to use the Date function to aggregate the data to weekly?
See below output example, record 1 aggregates all values from 1/01/2002 to 7/01/2002 for the selected district area (value of district field). The next data record aggregates all values from 8/01/2002 to 14/01/2002 for the selected district area (value of district field) and so on.
Is there a way to accompish this I haven't been able to find a solution.
I've tried pivot tables in MS Excel but having issues with the district field, haven't been able to get the district into a column like below. The feature to group days is great.
i.e.
Date District TotalLoss TotalHectare TotalCount
1/01/2002 1 50 100 150
14/01/2002 1 50 100 150
Thanks
ASKER
Thanks - what do you mean by ' bug for the ISO week numbering for week 53'?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all.
I've decided to go ahead with Fyed's solution.
I've decided to go ahead with Fyed's solution.
Note however, that VBA has a bug for the ISO week numbering for week 53.
/gustav