Condition across various dimensions in chart

[rob_vander]

Hi All,

I have below dataset. I want to have a sales for customer, where customers have minimum rank, for that minimum rank check earliest date and for that date consider largest sales amount. For eg. Customer XYZ, minimum rank is 2, it has two records for earliest date 01/02/2023 for two products out of which I need to consider largest amount of 65 for product bb. Likewise I need to apply samelogic for all customers

Load * Inline [
Customer,Rank, Date,Product, Sales
ABC,1, 01/03/2023,aa, 100
ABC,2, 16/03/2023,aa, 50
ABC,2, 11/03/2023,bb, 70
ABD,2, 20/04/2023,cc, 90
ABD,2, 20/04/2023,dd, 70
BCD,1, 01/04/2023,aa, 110
BCD,1, 02/04/2023,dd, 45
BCD,2, 19/04/2023,ee, 90
BCD,3, 18/04/2023,dd, 70
XYZ,2, 01/02/2023,bb,65
XYZ,2, 01/02/2023,cc,45
XYZ,2, 07/02/2023,cc,80
XYZ,3, 10/02/2023,ff,35
XYZ,3, 10/02/2023,gg,45 ]

output I need

Customer Rank  Date        Product  Sales
ABC       1    01/03/2023   aa      100
ABD       2    20/04/2023   cc      90
BCD       1    01/04/2023   aa      110
XYZ       2    01/02/2023   bb      65

Note:  I want to achieve this using set expression or any front end expression

[Kushal_Chawda · Accepted solution]

@rob_vander  try below expression with Dimension Customer, Rank, Product, Date

=if((Rank*1e10) + (Date*1e4) - (rank(total -Sum(Sales),4,1)) = 
min(total <Customer> aggr((Rank*1e10) + (Date*1e4) - (rank(total -Sum(Sales),4,1)),
Customer,Rank,Date,Product)),sum(Sales))

[MayilVahanan]

Hi

In that case, instead of max, use sum(Sales)

[rob_vander]

@Kushal_Chawda  Thanks It works as expected

[rob_vander]

@MayilVahanan  Sum(Sales) is not working in below scenario. If I use sum(Sales), it gives 80 and also maps it with product cc. It should give product dd with 90

XYZ,2, 01/02/2023,cc,20
XYZ,2, 01/02/2023,cc,60
XYZ,2, 01/02/2023,dd,90
XYZ,2, 07/02/2023,dd,80
XYZ,3, 10/02/2023,ff,35
XYZ,3, 10/02/2023,gg,45

I know you can make the expression, but expressions suggested by @Kushal_Chawda  is working as expected so I am going to use it for now. Thanks for helping me out