table with latest invoices

[#1]

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

[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))

[vkish16161]

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;

[sunny_talwar]

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

[#5]

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

[sunny_talwar]

How about this? This also did not work?

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