Expression problem

[#1]

Hi

I am working with accounts receivable forecast report. I have the following script in my pivot chart expression:

If((#InvoiceDate+[Due date])>=MonthEndDate And [Period in Month]<=Month,

The idea of this expression is that it would be looking which sales has been invoiced but not paid yet in the month end. This works very nicely as long as invoices would be paid within next month. However, quite often the case is that payment term is 45 days or even 60 days from the invoice date, which means that these sales should be seen not only in the "sales month" but also in the next month end. My script can't handle these cases.

Example:

There is invoice of 1000 eur in 25.5.2013 with payment term 45 days, so invoice will be paid in the July 9th 2013. This should be seen as accounts receivables both in May and in June.With my formula it can be only seen as accounts receivables in May, but not in June anymore. Any ideas how to change the script to handle this issue?

Thanks

Regards

Janne

[RedSky001]

If you'd like some help with this I'd suggest you provide some example actual data to work with.  It's difficult to understand your question without this.  (For example what is in MonthEndDate  /  [Period in Month] - these are calculated fields, it's not obvious (well to me anyway).

[#3]

I have now attached example. Hopefully that clarifyis the problem.

AS you can see in this case I can see "Accounts Receivables" only in May although the orderID 2 will be paid in July and that means that it would be reported as receivables still in end of June.

• Receivables in the end of May 3000+1000=4000 is correct cause orderID 3 is already paid in May 15th
• There should be receivables under June also (1000) cause OrderID 2 is due in July 9th.

Any ideas how to get that June reported also?

Thanks

Regards

Janne

[Gysbert_Wassenaar]

See attached qvw.