Loop formula 1

[neilmcdonald]

Apologies for the long-winded question below...

Our clients sign 3 year contracts and are invoiced monthly, quarterly and annually. I need to create a report to find out what income is due each month for the next 3 years. Our database stores the invoice frequency and I have calculated the last invoice date.

The only solution I can come up with is to create 36 separate formulae, one for each month which work back from the invoice date and check to see if that client will be invoiced in that month.

My formula for month 1 would be something like this:

Local NumberVar i;
For i:=1 to 36 Do
If DateAdd("m",-i,{LastInvoiceDate} in {ReportDate} to DateAdd("m",1,{ReportDate} Then
{Invoice Value} Else 0

My only problem is that the formula only ever returns TRUE or FALSE, and is ignoring the Then...Else part of the formula.

If anyone can see what's wrong (or knows a better way to do this), please let me know.

Apologies again for the question length...

 

[neilmcdonald]

I think the formula's completely wrong - it only ever returns TRUE.

 

[mbarron]

Your loop will, in it's current configuration only return the result for the last looping. You would need to put a stop - in blue below) It is also needs a value holding variable that will display the result.

The following folrmula will loop through the 36 months, and once the months match, it grabs the Invoice Value and ends the loop - displaying the Invoice Value.


Local NumberVar i;
local numbervar out;
For i:=1 to 36 Do (
If DateAdd("m",-i,{@LastInvoiceDate}) in {@ReportDate} to DateAdd("m",1,{@ReportDate}) Then
out:={@Invoice Value} Else out:=0;
if DateAdd("m",-i,{@LastInvoiceDate}) in {@ReportDate} to DateAdd("m",1,{@ReportDate}) then i:=37);
out


Mike

 

[neilmcdonald]

Thanks Mike - that seems to have done the trick!

 

[neilmcdonald]

Mike - is it possible to make the formula take into consideration the invoice frequency? The above formula works great for monthly invoices. Could I use if...then statements to check the invoice frequency and then use variations of your formula?

Thanks again for your help,

Neil

 

[mbarron]

You can do this with a "step"

local numbervar out;
local numbervar freq :=0;

if {interval type} = "monthly" then freq:=1 else
if {interval type = "quartetly" then freq:=3;


For i:=1 to 36 step freq Do (





Mike

 

[neilmcdonald]

Once again, thanks for your help Mike.