Search code examples
qliksense

Expression filtering based on user input

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 DatePicker user input in the Sheets editor, 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 (for visualisation):

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

Is there a possible solution or workaround for what I need?

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.

Thanks in advance!

Solution

  • You can use this expression:

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

    It works in various kinds of objects like table or KPI:

    enter image description here

    Data used:

    Data:
Load *
Inline [
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
];