Analyzing Variable depending from another variable

[J_Uri]

I'm having issues with a set analysis regarding a calculation based on a Variable.

To put into context, this is what i'm trying to do:

I have the need to filter by a field based on certain hours

Load * Inline [

Date, Hour, Order, Field, Sales

2024-06-14, 06:30, 1, A, 20

2024-06-14, 08:30, 2, A, 10

2024-06-14, 10:30, 3, A, 5

2024-06-14, 07:00, 1, B, 15

2024-06-14, 08:00, 2, B, 10

2024-06-14, 09:00, 3, B, 5

2024-06-14, 10:00, 4, B, 20

2024-06-13, 06:30, 1, A, 20

2024-06-13, 08:30, 2, A, 10

2024-06-13, 10:30, 3, A, 5

2024-06-13, 07:00, 1, B, 15

2024-06-13, 08:00, 2, B, 10

2024-06-13, 09:00, 3, B, 5

2024-06-13, 10:00, 4, B, 20

];

SO for example, with this data, i need to get the results of Sales for the last Day and Latest Hour, and also the results for sales for the previous hour.

For now i'm using a variable that stores the order of execution and using that into my set analysis.

variable --> Max({<Date={"$(=Max(Date))"}>}Order) --> Which gives me the latest value for the selected day

And then use this variable to get the Sales:
Sum({<Order={'$(variable)'}>}Sales).

However, i need to display the Hours instead of the Order fields in the Variable Input. And this needs to update dynamically, so i can't use the Order value and just manually change the label.

So i have to options:

1) Use Set Analysis to obtain the text value of Hour field for the Max(Order) (using also additional filters such as Field in the example table). I've tried with Aggr(Only(SETANALYSIS)) and other aggregate functions but couldn't make it work.

2) Use Order field and Manually change these labels.

Any ideas?

Thanks in advance.

[Chanty4u]

Try this 

variable_MaxOrder:

=Max({<Date={"$(=Max(Date))"}>} Order)

variable_MaxHour:

=Aggr(Only({<Date={"$(=Max(Date))"}, Order={"$(=variable_MaxOrder)"}>} Hour), Field)

Final exp

Sum({<Hour={'$(=variable_MaxHour)'}

>} Sales)

[J_Uri]

Sadly, didn't work.

I need to filter using variables as buttons, using a text field containing hours but actually be filtering by Order.

This means that the user selects a variable text value, which then needs to be transformed to a numeric value, which would be used for the rest of the calculations.

I'm trying nesting variables, which would use a variable taking the value of another value and converting it to another value matching the corresponding order value.