Sum of result for today or before

[pauledrich]

Hi there;

I seem to have issues getting my head around dates and need a little help.

I want to calculate the total amount of Invoices which have not been repaid up to today (Expired) where the status is current, the script loads:-

Load

Date (Floor(ExpiryDate)), 'DD/MM/YYYY' as ExpDate,
    ExpiryDate,
    Month (ExpiryDate) as EMonth1,
    MonthName (ExpiryDate) as EMonth,
    Year (ExpiryDate) as EYear,

InvoiceAmount,

InvoiceStatus

From.......

I have the following:-

Sum ({$<ExpDate={>$(<=(Today)), Status={C}}>}InvoiceAmount)

My table is blank...

Any help along with a description of the solution would be appreciated.

Regards

P

[sparur]

Paul, could you provide qvw example with your issue?

[CELAMBARASAN]

Hi Paul

could you tell me what is the date format for the field ExpDate you used?

use the same format in the below instead of (DD/MM/YYYY) and check.

=sum({<ExpDate={"<=$(=Date(today(),'DD/MM/YYYY'))"}, Status={'C'} >} InvoiceAmount)

[pauledrich]

Hi Thanks for the reply.

I found the issues.

1. You were correct in that the syntax was incorrect with the <= (Thank you)

Also

2. The script had:-

(Floor(ExpiryDate)),'DD/MM/YYYY' as ExpDate,
    ExpiryDate,

(Floor(ExpiryDate),'DD/MM/YYYY') as ExpDate,
    ExpiryDate

Another very useful piece of learning.

Thanks all.

[pauledrich]

Hi Thank you for your help.

Why is the position of the ")" incorrect in the original syntax and important to the expression I was trying to do?