Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
rob_vander
Creator
Creator

Complex expression with multiple condition

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
Labels (4)
1 Solution

Accepted Solutions
Kushal_Chawda

@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))

 

Screenshot 2024-11-06 at 10.40.51.png

View solution in original post

3 Replies
chriscammers
Partner - Specialist
Partner - Specialist

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

@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))

 

Screenshot 2024-11-06 at 10.40.51.png

rob_vander
Creator
Creator
Author

it works. thanks