Complex expression with multiple condition

[rob_vander]

Hi All,

I have below data set. I want to extract highlighted row in table chart. Basically for each category, I need to pick minimum date, for that date I need to pick minimum rank, and for that rank I need to pick maximum value. Could anyone help me to get the correct expression?

Note: I need to do it using expression as I don't want to change the script.

Category	Date	Rank	Sub Category	Value
A	10/01/2024	1	aa	100
A	10/01/2024	1	aa	50
A	10/01/2024	2	bb	110
A	15/01/2024	1	aa	90
A	15/01/2024	2	bb	70
A	15/01/2024	3	cc	60
B	10/01/2024	2	bb	90
B	10/01/2024	2	bb	150
B	10/01/2024	3	cc	60
B	14/01/2024	1	aa	100
B	14/01/2024	2	bb	45
B	14/01/2024	3	cc	75

[chriscammers]

That is a really rough requirement, the multi level selection is really tough. My suggestion is to explore chart level scripting.

https://community.qlik.com/t5/Design/Chart-Level-Scripting-Use-Cases-Samples-and-Examples/ba-p/19810...

I don't have any experience with it so I'll have to let others try to explain it but it will probably have the flexibility you need.

Good Luck

[Kushal_Chawda · Accepted solution]

@rob_vander  it might be complex but you can do it with below expression

=sum(aggr(if(Date*1e9 + Rank*1e5 -sum(Value) = min(total <Category>aggr(Date*1e9 + Rank*1e5 -sum(Value),Category,Date,Rank,[Sub Category],Value)),sum(Value)),
Category,Date,Rank,[Sub Category],Value))

[rob_vander]

it works. thanks