# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Creator

## Sum of result for today or before

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:-

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

1 Solution

Accepted Solutions
Highlighted
MVP

Hi Paul

Wrong placement of <=

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

Hope it helps

13 Replies
Highlighted
Specialist II

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)

Highlighted
MVP

Hi,

Check with this

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

Highlighted
Creator

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.

Highlighted
Partner

Hi,

Try below syntax.

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

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

into

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

Highlighted
Specialist II

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

Highlighted
Creator

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)

Highlighted
Specialist II

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

Highlighted
MVP

Hi Paul

Wrong placement of <=

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

Hope it helps

Highlighted
Creator

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