Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
)
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
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
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