Rolling 12 Months 1

[bslaast]

I need a formula to calculate a rolling 12 months. I have a number parameter where the user will enter a year/period number like 201008. That would be the first column and the next 11 columns would be 201007 201006 201005 201004 201003 201002 201001 201000 200912 200911 200910 200909 200908.

 

[IanWaterman]

Convert to a true date first

@Date
Date(left({?yourparam},4), right({?yourparam},2), 01)

For Column 200908

Use formula
@200908
If year(yourdatefield) = year(dateadd("m",-12,{@date})) and
month(yourdatefield) = month(dateadd("m",-12,{@date})) then yourmeasurefield

Repeat logic for other columns

Ian

 

[bslaast]

2 problems:

My parameter is a number, not a string.

Even if I do change it to a string I get this error - "Too many arguments have been given to this function" right({?YTD Current Year Period End},2), 01

 

[lbass]

I think it would help if you identified your columns by the convention {table.name} and then also showed a sample of what is displayed in these columns.

-LB

 

[bslaast]

Group 1: {Account.Account}
Group 2: {Department.Descriptoin}
Running total in GF2: {ACCOUNTING_BALANCE\.YEAR_PERIOD_KEY} = {?YearPeriod}

If the user enters 201008 for {?YearPeriod}:

201008 201007 201006 201005 201004...to 200908

1000 500 699 1200 987

What I want is the user to only enter 1 date. This date would be used in the first column's running total. The other 11 column's running totals would have a formula that will calculate the prior periods - 201007 201006 201005 201004 201003 201002 201001 200912 200911 200910 200909 200908

 

[bslaast]

I don't feel like I'm explaining it well. It is a report that displays account balances by month. I need a formula that would calculate the previous 12 months based off the parameter number entered. I will use the formulas in each running total for every month

 

[lbass]

Are:

201008 201007 201006 201005 201004...to 200908

...all instances of one field {ACCOUNTING_BALANCE\.YEAR_PERIOD_KEY}? Or are they all separate fields? I'm unclear what each contains, also.

-LB

 

[IanWaterman]

If your parameter is now a string this formula should work

@Date
Date(left({?yourparam},4), right({?yourparam},2), 01)

The segment you showed above

right({?YTD Current Year Period End},2), 01

will not work

Ian

 

[bslaast]

They are all instances of one field. The parameter retreives the first instance, and I want to calculate the prior 12 instances. I am using the parameter in a running total. I would replace the parameter with the formula in the other running totals.

Month 1: {?YearPeriod} = 201008 Running total is a sum on {ACCOUNTING_BALANCE\.AMOUNT_BALANCE} when{ACCOUNTING_BALANCE\.YEAR_PERIOD_KEY} = {?YearPeriod}

Month 2: {?YearPeriod}-1 = 201007 Running total is a sum on {ACCOUNTING_BALANCE\.AMOUNT_BALANCE} when{ACCOUNTING_BALANCE\.YEAR_PERIOD_KEY} = {?YearPeriod}-1

Month 3: {?YearPeriod}-2 = 201007 Running total is a sum on {ACCOUNTING_BALANCE\.AMOUNT_BALANCE} when{ACCOUNTING_BALANCE\.YEAR_PERIOD_KEY} = {?YearPeriod}-2

Month 4: {?YearPeriod}-3 = 201007 Running total is a sum on {ACCOUNTING_BALANCE\.AMOUNT_BALANCE} when{ACCOUNTING_BALANCE\.YEAR_PERIOD_KEY} = {?YearPeriod}-3

{?YearPeriod}-X does not always work. Going from the first period of this year, to the last period of last year 201001 - 1 = 201000, when it needs to be 200912.

 

[bslaast]

Ian,

I did use the whole formula you orignally gave me, its just the second part that is getting the error of "Too many arguments have been given to this function." So, no, Date(left({?yourparam},4), right({?yourparam},2), 01) does not work.

 

[lbass]

Ian, you have to wrap the string results in val().

Try this in the evaluation area of your running totals, just changing the n in each one:

numbervar n := 4; //set the month in each formula, e.g., 4 for four months ago
numbervar x := 0;
if val(right(totext({?YearPeriod},"00"),2))-n <= 0 then
x := {?YearPeriod}-100+12-n else
x := {?YearPeriod}-n;
{ACCOUNTING_BALANCE\.YEAR_PERIOD_KEY} = x

-LB

 

[IanWaterman]

Sorry LB is correct formula must use numbers, you can also use tonumber on elements

@Date
Date(tonumber(left({?yourparam},4)), tonumber(right({?yourparam},2)), 01)

Place each of the Month formula in details, suppress details and then do a sum on each formula to give you your required result.

Also forgot the else 0, should be

@200908
If year(yourdatefield) = year(dateadd("m",-12,{@date})) and
month(yourdatefield) = month(dateadd("m",-12,{@date})) then yourmeasurefield else 0

Ian

 

[bslaast]

Thanks, LB that worked perfectly.

Ian - it cannot be a date. Also, you cant sum on
Date(tonumber(left({?yourparam},4)), tonumber(right({?yourparam},2)), 01)

 

[bslaast]

How do I get the variable to display in the page header?

 

[lbass]

What is it that you want displayed in the Page Header?

-LB

 

[bslaast]

the result of the formula - 201008, 201007, 201006 etc

 

[lbass]

You would have to create twelve formulas like this:

numbervar n := 4; //set the month in each formula, e.g., 4 for four months ago
numbervar x := 0;
if val(right(totext({?YearPeriod},"00"),2))-n <= 0 then
x := {?YearPeriod}-100+12-n else
x := {?YearPeriod}-n;
x

-LB

 

[bslaast]

Great, Thanks.