Formula for running total 1

[mtepfer]

Am using CR9, have about 40 lines of data. One data attribute contains a number which represents a number of months. Want to take this number subtract it from the current month and add a counter of +1 to that month and loop until that number is zero then move onto the next line. Continue this process until all data lines have been read and then put a total of the counters for each month in the display.

Data example
Client ID Retro Months
1 2 (april,may)
2 3 (march,april,may)
3 1 (may)

Since the current month is June (6) for the first line a counter of one would be added to April (4) and May (5), next line another counter of one would be added to march april and may and so on. The display would then show the following

May 3
April 2
March 1

Hope this makes sense, am not very good at programming in this yet.
My thinking is to read the first data element, have a do while loop until the retro hits zero (counter subtracting one after each process), in that loop calculate the appropriate month by subtracting retro months from current month (with retro decreasing by one after each pass), then possibly using a case statement to add a counter for the month, then repeat for the next data element.

Thanks for any help

Mike

 

[synapsevampire]

I think that you might use 12 formulas to count the number of month occurences, at least it's one way to do so:

Replace the -2 with your database field:

numbervar Jan;
if month(dateadd("m",-1,currentdate)) >= month(dateserial(year(currentdate),month(currentdate)-2,1)) then
(
if 1 in
month(dateserial(year(currentdate),month(currentdate)-2,1))
to
month(dateadd("m",-1,currentdate))
then
Jan:=Jan+1
)
else
if 1 in
(
month(dateadd("m",-1,currentdate))
to
12
)
or
1 in
(
month(dateserial(year(currentdate),month(currentdate)-2,1))
to
month(dateadd("m",-1,currentdate))
) then
Jan:=Jan+1;

You might also use an array to store this which might add some elegance.

-k

 

[synapsevampire]

Oh, and the "if 1 in" portion will increment each month, as in 2 being feb, 3 mar, etc.

-k