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

# table with latest invoices

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

Alberto

• ###### Re: table with latest invoices

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

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)

Alberto

• ###### Re: table with latest invoices

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

• ###### Re: table with latest invoices

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;

• ###### Re: table with latest invoices

Efficient, but then we loose the flexibility to making selections... for instance if the user wants to select a particular month and view the max based on that month.... it won't work. Or if the user wants to select a region.... But if this is static table... then script is a good option