APO DP macro to sum data for quarters of years

[Former Member]

I am using APO DP V5.

I have a macro requirement to populate a key figure x as follows:

For a given weekly time bucket in the future:

- determine the quarter in which it lies

- look at history data (which covers several years) and sum the quantities for key figure y for those weeks in previous years which lie in the relevent quarter

- put this summed quantity in key figure x

So, for example, where we have 3 years of history:

- weekly time bucket (in year 2007) is in quarter 3

- need to sum quantities for key figure y for those weeks in years 2004, 2005, 2006 which lie in quarter 3

- put this summed quantity in key figure x

Any ideas appreciated!

Regards,

Bob Austin, Atos Origin

[Former Member · Accepted solution]

Hello Bob -

Why dont use the date function QUARTER()

Syntax

QUARTER ( Number of days )

Number of days: Number of days since 0001/01/01.

Description

QUARTER() returns the quarter (as a number 1 to 4) in which the date, expressed as the number of days since 0001/01/01, lies. You can convert a date in the normal format to the number of days format by using functions DATE or TODAY.

that shud help you in getting started atleast.

Hope this helps.

Regards,

Suresh Garg

[Former Member]

You will have 12 values for "sum of all weeks in quarters" put together in the last 3 yrs.

Do you want to populate them in the weekly buckets for the corresponding quarter? So 10 or 11 buckets will have the same summed value?

the solution i can think of needs too many auxillary rows and macro steps

What you can do is create an auxilliary row A

A is populated using macro Quarter (as mentioned by suresh) so week2 2004 will have 1 and so will week11.2004 and so will week 11.2007

Use

Auxilliary Row B - if Row A = 1, copy value of KF Y

Auxilliary Row C - if Row A = 2, Copy value of KF Y

Auxilliary Row D - if Row A = 3, Copy value of KF Y

Auxilliary Row E - if Row A = 4, Copy value of KF Y

In the last (or any current) cell of B,C, D and E add all the previous cells in the row

this will enable you to get in one place the sum of the quarters (how you manage to keep this in the three yr horizon is something that needs thinking)

Now in KF X write a macro that says

If value of corresponding cell in A is 1 then Get value of last cell in B

If value of corresponding cell in Ais 2 then Get value of last cell in C

If value of corresponding cell in A is 3 then Get value of last cell in D

If value of corresponding cell in A is 4 then Get value of last cell in E

hope that was not too confusing...