Find the Max Date and the corresponding Product per Customer

[#1]

Hi everyone! Can someone please help me on the following? My data are like the below table:

Customer	Date	Product
CustomerA	1/1/2015	ProductA
CustomerA	1/5/2015	ProductB
CustomerB	20/5/2015	ProductA
CustomerB	1/3/2015	ProductC
CustomerC	10/4/2015	ProductB

I need to have per Customer the maximum Date and the corresponding Product. So the result will be like this:

Customer	Date	Product
CustomerA	1/5/2015	ProductB
CustomerB	20/5/2015	ProductA
CustomerC	10/4/2015	ProductB

Any ideas? I would prefer to implement this through Dimensions and expressions and not through the script of my qv document....

[jonathandienst]

Make a table with Customer as dimension and these two expressions:

• Max(Date)
• FirstSortedValue(Product, -Date)

[PrashantSangle]

Hi,

Use FirstSortedValue()

Try like in chart FirstSortedValue(Product,Date)

Regards

[Anonymous]

Use aggr() function

[Siva_Sankar]

PFA

[#6]

Hii, Please find the attachment below..

[#7]

Thank you Jonathan, it works but in some cases it displays the value "-" on column Product. I guess it is because at the same day, it may find two products and don't know which one to choose. Any suggestions?

[tresesco]

Could try like:

FirstSortedValue(Distinct Product, -Date)

Note: Don't worry about the warning at the expression because of this 'distinct' keyword , it works fine.

[PrashantSangle]

In that case, Which Product you want???

Regards

[#10]

Thanks guys! Distinct was the solution to my problem! have a nice day!