Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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]
)
)
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]
)
)
@bubbleT Perhaps this?
Max({<Date={"<=$(vSelectedDate)"}>} Total <Date> Aggr( Sum({<Date={"<=$(vSelectedDate)"}>} Revenue*Score), [Organisation Name]))
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.