Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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;
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
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
How about this? This also did not work?
FirstSortedValue(Aggr(Concat([Inv#], ','), [Inv Date], Customer), -Aggr([Inv Date], [Inv Date], Customer))