Expression filtering based on user input

[bubbleT]

I wish to aggregate Score * Revenue for the most recent entry per organization, considering only those entries where the date is less or equal to a user-defined variable vSelectedDate that is from the user input DatePicker in the Sheets section after load script has completed.

Organisation Name	Date	Revenue	Score
Apple	06/02/2024	300	5
Apple	06/02/2023	200	4
Apple	06/02/2022	100	3
Banana	01/01/2024	50	10
Banana	01/01/2023	70	10
Banana	01/01/2022	100	11

E.g.vSelectedDate= 30/12/2023. Desired selection:

Organisation Name	Date	Revenue	Score
Apple	06/02/2023	200	4
Banana	06/02/2023	70	10

Desired expression result: 200 * 4 + 70 * 10 = 1500

I have tried:

Sum(
  Aggr(
    If(
      Rank(Max(Date)) = 1 and Date <= $(vSelectedDate),
      Score * Revenue
    ),
    [Organisation Name]
  )
)

However I understand that it doesn't work because Max(Date) needs to be calculated within an aggregated context specific to each [Organisation Name], but without an explicit aggregation phase separating the determination of Max(Date) from its ranking, the expression cannot correctly isolate and rank the latest date per organization.

Is there a possible solution or workaround for what I wish to achieve?

Thanks in advance!

[LRuCelver · Accepted solution]

You can use a set expression to restrict the dates:

{<Date={"<=$(vSelectedDate)"}>}

You might have to play around with date formatting a bit if it doesn't work right away. I used the default date format for the field and variable and it worked.

To get the most recent values for the Revenue and Score you can use the FirstSortedValue function and sort the fields by the most recent Date:

FirstSortedValue(Revenue * Score, -Date)

To calculate the product for every organization, use Aggr and then sum everything up to get the final result:

{<Date={"<=$(vSelectedDate)"}>} 
Sum(
    Aggr(
        FirstSortedValue(Revenue * Score, -Date), 
        [Organisation Name]
    )
)

[Anil_Babu_Samineni]

@bubbleT Perhaps this?

Max({<Date={"<=$(vSelectedDate)"}>} Total <Date> Aggr( Sum({<Date={"<=$(vSelectedDate)"}>} Revenue*Score), [Organisation Name]))

[bubbleT]

Much appreciated. For anyone in need, I ended up using:

Sum(
    Aggr(
        FirstSortedValue({<Date={"<$(vSelectedDate)"}>} Revenue * Score, -Date), 
        [Organisation Name]
    )
)

which works exactly as intended.