5 Replies Latest reply: Oct 16, 2017 7:23 AM by Sunny Talwar RSS

    table with latest invoices

    Alberto Pittaluga

      Hello

       

      I would need some help on obtaining the following:

       

      I have a table with all invoices from all customers. Eg:

       

      Customer,  Inv Date,  Inv#,  Inv Value

      A                 1/1/17      100    100

      A                 1/3/17       180    200

      B                 1/1/17       101    200

      B                 1/8/17       300    100

      B                 1/10/17     500    100

       

      What I would like is a table with just the latest invoice per each customer. So in my example:

       

      Customer,  Inv Date,  Inv#,  Inv Value

      A                 1/3/17       180    200

      B                 1/10/17     500    100


      I got tangled with expressions... Please help


      Thanks in advance


      Alberto

        • Re: table with latest invoices
          Sunny Talwar

          Create a table with the following

           

          Dimension

          Customer

           

          Expressions

          1) Date(Max([Inv Date]))

          2) FirstSortedValue([Inv#], -[Inv Date])

          3) FirstSortedValue([Inv Value], -[Inv Date])

           

          This is assuming that each customer will only have one Inv# and Inv Value on a day... if they have multiple... you can try this

           

          Dimension

          Customer

           

          Expressions

          1) Date(Max([Inv Date]))

          2) FirstSortedValue(Aggr(Concat([Inv#], ','), [Inv Date], Customer), -Aggr([Inv Date], [Inv Date], Customer))

          3) FirstSortedValue(Aggr(Sum([Inv Value]), [Inv Date], Customer), -Aggr([Inv Date], [Inv Date], Customer))

            • Re: table with latest invoices
              Alberto Pittaluga

              Thank you for your answer Sunny

               

              I tried and it won't work.

               

              What happens when I create a new table in a new sheet is

              - I add the Dimension field with customer names, ok of course

              - I add the first calculated field with Date(Max([Inv Date])) and a column with unique (latest) invoic number is added. OK

              - Then I try to add a further column with FirstSortedValue([Inv#], -[Inv Date]) and I get an empy column (actually a fully greyed out column)

               

              thanks for your help

               

              Alberto

               


            • Re: table with latest invoices
              Vishnu Chakravaram

              Try doing this at the script level. It's more efficient.

               

              A:

              Load Customer, Inv Date, Inv#, Inv Value

              From Customer.blahblah

               

              Left Join (A)

              Load Customer, Max( num( Inv Date) ) as Flag

              Group by Customer

              Resident A;