Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Finding last invoices date and time

Hi all.

I'm completely new to QV, so the following question may be easy as pie. If so, sorry in advance.

To verify data integrity, I'd like to find and display date and time of the last imported  invoice. Assuming that we have invoices every single work day, the  result of my formula should be the date of the last work day before  today.

I thought of using s th like the following, but that obviously didn't work:

if([invoice_nr]>0,                                                  // If the record is an invoice, not order or return

firstsortedvalue([date],[invoice_nr],-1)                // find me the date to the last invoice

)

4 Replies
swuehl
MVP
MVP

Try just this as expression:

=FirstsortedValue([date], -[invoice_nr])

Firstsortedvalue sorts by -[invoice_nr] and will return record with the lowest value, thus I negated the nr to actually get the highest invoice_nr. I think you don't need to check for positive invoice_nr, if you have any in your record set at all (if you need to check, use a set expression instead). Not sure why you used a third parameter, '-1' to firstsortedvalue, could you explain that?

Hope this helps,

Stefan

Miguel_Angel_Baeyens

Hi,

If it's an expression, you can use set analysis to select only invoices:

FirstSortedValue({< [invoice_nr] = {">0"} >} [date], -[invoice_nr])

Hope that helps.

Miguel

Not applicable
Author

First, let me thank you for your support. In fact, both ways wouldn't work, for the following reasons:
  1. We use a concatenated table 'distribution', where all orders, shipping notes, invoices, credits and returns are combined.
  2. [invoice_nr] is not actually a number, but a string. It contains 'RG-12345' for invoices, 'GS-12345' for credits and is empty for orders, returns and shipping notes. For data integrity reasons i need to find the last invoice (or credit).

So my intention is to find the newest table entry where [invoice_nr] is not empty ( either 'RG:*' or 'GS-*', which tells me it must be an invoice or credit) and return it's date and time.
Miguel_Angel_Baeyens

Hi Marcus,

Then you need something like

FirstSortedValue({< [invoice_nr] = {"RG*", "GS*"} >} [invoice_nr], -[date])

That will return the invoice_nr value where it starts either by RG or GS and date has the highest value (hence the "-" sign before the field name, like "sort descending).

Depending on your data model the expression

Max({< [invoice_nr] = {"RG*", "GS*"} >} [date])

will return the highest (latest) value of [date] for an invoice starting on RG or GS.

When concatenating from several types of facts, creating a numeric flag field is usually a good idea and will save some render time in the charts. So for example, instead of doing a text search in the set analysis as above, you have a numeric value for orders, invoices and credits, say 1, 2 and 3, and the expression will be

FirstSortedValue({< [FactType] = {1, 2} >} [invoice_nr], -[date])

Hope that helps.

Miguel