Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
@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))
That is a really rough requirement, the multi level selection is really tough. My suggestion is to explore chart level scripting.
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
@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))
it works. thanks