Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
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
Partner - Champion

Hi Paul

Wrong placement of <=

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

Hope it helps

13 Replies
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)

Partner - Champion

Hi,

Check with this

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

Creator
Author

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.

Partner - Specialist

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,

Specialist II

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

Creator
Author

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)

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

Partner - Champion

Hi Paul

Wrong placement of <=

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

Hope it helps

Creator
Author

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

Community Browser