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

[Chanty4u]

Try this 

Customer={'$(=Aggr(FirstSortedValue({<Rank={'$(=Min(Rank))'}, Date={'$(=Min(Date))'}>} Sales, -Sales), Customer))'}

[MayilVahanan]

Hi

You can try like below

Dim: Customer, Rank, Date

Exp 1: If(Rank = Min(Total<Customer> Rank), if(Date = Min(Total<Customer> Date), Max(Sales)))

Exp 2: If(Rank = Min(Total<Customer> Rank), if(Date = Min(Total<Customer> Date), FirstSortedValue(Product, -Sales)))

[Paula1]

Mayil Vahanan 

https://community.qlik.com/t5/New-to-Qlik-Sense/Script-duplicate-data/td-p/2010608

question on the forum

https://community.qlik.com/t5/App-Development/Duplicate-record-if-it-gets-status-1/m-p/2069355#M8768...

Mayil Vahanan, How can I use your answer in my query below.

Any suggestion

If the Status is -1, it duplicates the line, changing it to positive.

Edit Qty and Status to positive and duplicate the line, 

id, date, status, ordem, qtde

2360	12/11/2012 17:33:13	-1	201221	-24
2360	12/11/2012 17:33:13	-1	201222	-84
2360	12/11/2013 17:38:13	-1	201228	-24
2360	12/11/2013 17:38:13	-1	201227	-84

How should it look

2360	12/11/2012 17:33:13	-1	201221	-24
2360	12/11/2012 17:33:13	-1	201222	-84
2360	12/11/2013 17:38:13	-1	201228	-24
2360	12/11/2013 17:38:13	-1	201227	-84
2360	12/11/2012 17:33:13	0	201221	24
2360	12/11/2012 17:33:13	0	201222	84
2360	12/11/2013 17:38:13	0	201228	24
2360	12/11/2013 17:38:13	0	201227	84

[rob_vander]

@MayilVahanan  thanks for your reply. I don't want to use product as measure because user want it as dimension to select from that report. Is there possibility to have product as dimension?

[MayilVahanan]

Hi

In that case, one of the methods to achieve this.

Dim: Customer, Rank, Date,Product

Exp: If(Rank = Min(Total<Customer> Rank), if(Date = Min(Total<Customer> Date) and Product = FirstSortedValue(Product, -Aggr(Sum(Sales), Customer)), Max(Sales)))

[vishalarote10]

For your case largest amount for XYZ is 80 not 65.

Please go through the below code.

A:
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 ];

B:
Load Customer&'-'&Min(Rank)&'-'&Max(Sales) as Key
Resident A
Group by Customer;

NoConcatenate
C:
Load *
Resident A
where Exists(Key,Customer&'-'&Rank&'-'&Sales);
Drop table A,B;

Output:

[rob_vander]

@Chanty4u  it's not working

[rob_vander]

@vishalarote10  I want to do it on front end no script. 

[rob_vander]

@MayilVahanan  Thanks for your reply but in case of below data it returning wrong value.  It should get sales value as 110 but it is giving 65

XYZ,2, 01/02/2023,cc,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