4 Replies Latest reply: Jan 17, 2012 7:17 AM by Miguel Angel Baeyens de Arce RSS

    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

      )

        • Finding last invoices date and time
          Stefan Wühl

          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

          • Finding last invoices date and time
            Miguel Angel Baeyens de Arce

            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

              • Finding last invoices date and time
                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.
                  • Re: Finding last invoices date and time
                    Miguel Angel Baeyens de Arce

                    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