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]

Hi Paul

What is Today in set analysis?

If you have no special variable, I think you need to use set analysis like this: sum({1<ExpDate = {">$(=Date(Today(), 'DD/MM/YYYY'))"}>} InvoiceAmount)

[CELAMBARASAN]

Hi,

Check with this

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

[pauledrich]

Hi,

Today is not a field - I only want to return all values prior to and including today, ExpDate could contain dates from any point in time before today and into the future.

[kumarnatarajan]

Hi,

Try below syntax.

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

But check your ExpDate field in your script.

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

into

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

[sparur]

Ok, so try to use my example, where I use system function Today().

[pauledrich]

Hi Thanks for the help so far...

Still no result with both of the solutions, however, the set below returns the information I need but it also provides all future to expire invoices.....

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

Changing the expression to

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

Returns no results.

[sparur]

could you attach your qvw example? I think the issue is with date formats, but without full picture it quite difficult to understand...

[CELAMBARASAN · Accepted solution]

Hi Paul

Wrong placement of <=

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

Hope it helps

[pauledrich]

Thanks for your help so far, none of the suggested solutions fix the problem, all invoice due dates thro to 2014 are displayed.

Completely stuck on this one and would appreciate help