Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Creator
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:-

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

1 Solution

Accepted Solutions
Highlighted

Hi Paul

Wrong placement of <=

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


Hope it helps

View solution in original post

13 Replies
Highlighted
Specialist II
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

Hi,

Check with this

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

Highlighted
Creator
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
Partner

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,

Highlighted
Specialist II
Specialist II

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

Highlighted
Creator
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)

Returns no results.

Highlighted
Specialist II
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

Hi Paul

Wrong placement of <=

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


Hope it helps

View solution in original post

Highlighted
Creator
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