Quicksight - Dynamic function lookup id

Question & Answer
1

Posting customer question (submitted by awn1995 via stack overflow) and couple of options to accomplish similar outcome.

Is it possible to use another function or a parameter in place of the absolute value in a lookup ID?

I have a table of data that looks like this:

Date Value
2022-01-01 1,264
2021-12-31 1,232
2021-12-30 1,221
2021-12-29 1,121

I’ve created two parameters to store date values, one called “latestdate” which just carries the latest date value (top of Date column) and another called “comparedate” which can be set to any date in the Date field.

My hope is to write a lag function that will display a Value from a specified comparedate in line next to the current date like so:

Date Value Lagged Value (from 2021-12-29)
2022-01-01 1,264 1,121

I’ve written the following function that I assumed would work:

lag(
    sum({Value}),
    [{Date} DESC],
    dateDiff(
        ${latestdate},
        ${comparedate},
        'DD')
    )

Where I’ve replaced the lookup index value, which would normally be a positive or negative number, with a dateDiff formula. However, I’m getting the same error every time:

“Expression {{argumentName}} for function {{functionName}} has incorrect argument type {{incorrectArgumentType}}. Function syntax expects {{functionSignature}}.”

Any ideas on how to create this type of dynamic lag or if there’s a way to set a dynamic lookup index?

2

I can think of couple of options to solve for this scenario.

If end objective is to display lagged values against all days, you can use following calculation.
The order should be ASC and parameter used is directly having the desired lag day count (integer value).
You can use the same parameter in an addDateTime calc to derive the latest comparison date if needed and display that in the dashboard as well.
Note that if the table is filtered to show only latest date, the lag calculation will display blank value. (Read further below if intent is to show just latest date with the compare value.)

Lag Value Calculation

lag(
    sum(Sales),
    [{Order Date} ASC],
    ${pLagDays},
    []
)

If intent is to show just one row with latest date, measure value from this date and measure value from compare date, you can accomplish this using a PRE_FILTER Level Aware Aggregate calculation.
The following calculation will sum up all sales from the compare date (here, you can feed the parameter with a date picker) and will make it available for use across all rows of data. Filtering the visual to show just the latest date will not impact the compare value in this case as we are specifying calculation level as PRE_FILTER.
Note - date field has been truncated to day grain. Parameter and control were setup at day grain and hence not being truncated again.

Compare Date Sales Calculation

sumOver(
        ifelse(
                truncDate('DD',{Order Date}) = ${pCompareDate}, 
                Sales,
                0 
        ), 
        [], 
        PRE_FILTER
)

Regards,
Arun Santhosh

2 Likes
3

Thank you for your help, Arun! The second option " Compare Date Sales Calculation" worked perfectly. Thanks for your assistance.

4

Most welcome. Glad to know that you are unblocked.

Regards,
Arun Santhosh

5

Hello @ArunSanthosh… First, thank you for your suggestion. It is really interesting. I’m just wondering if it is possible to use a column (text type) instead a calculation in this case. I 've already opened a question about it. Any thoughts?