# Quicksight - Dynamic function lookup id **URL:** \ **Category:** Q&A\ **Tags:** calculations, quick-sight\ **Created:** [January 19, 2022, 8:18pm UTC](https://community.amazonquick.com/t/quicksight-dynamic-function-lookup-id/1291 "2022-01-19T20:18:49Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![ArunSanthosh](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/arunsanthosh/32/3292_2.png) [@ArunSanthosh](https://community.amazonquick.com/u/ArunSanthosh)\ **Post date:** [January 19, 2022, 8:18pm UTC](https://community.amazonquick.com/t/quicksight-dynamic-function-lookup-id/1291/1 "2022-01-19T20:18:49Z")
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: ```auto 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? ---
**Author:** ![ArunSanthosh](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/arunsanthosh/32/3292_2.png) [@ArunSanthosh](https://community.amazonquick.com/u/ArunSanthosh)\ **Post date:** [January 19, 2022, 8:44pm UTC](https://community.amazonquick.com/t/quicksight-dynamic-function-lookup-id/1291/2 "2022-01-19T20:44:26Z")
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** ```auto 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** ```auto sumOver( ifelse( truncDate('DD',{Order Date}) = ${pCompareDate}, Sales, 0 ), [], PRE_FILTER ) ``` Regards, Arun Santhosh ---
**Author:** ![alexander.newbegin](https://avatars.discourse-cdn.com/v4/letter/a/51bf81/32.png) [@alexander.newbegin](https://community.amazonquick.com/u/alexander.newbegin)\ **Post date:** [January 20, 2022, 12:22am UTC](https://community.amazonquick.com/t/quicksight-dynamic-function-lookup-id/1291/3 "2022-01-20T00:22:30Z")
Thank you for your help, Arun! The second option " **Compare Date Sales Calculation**" worked perfectly. Thanks for your assistance. ---
**Author:** ![ArunSanthosh](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/arunsanthosh/32/3292_2.png) [@ArunSanthosh](https://community.amazonquick.com/u/ArunSanthosh)\ **Post date:** [January 20, 2022, 3:37pm UTC](https://community.amazonquick.com/t/quicksight-dynamic-function-lookup-id/1291/4 "2022-01-20T15:37:07Z")
Most welcome. Glad to know that you are unblocked. Regards, Arun Santhosh ---
**Author:** ![woliveiras](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/woliveiras/32/3080_2.png) [@woliveiras](https://community.amazonquick.com/u/woliveiras)\ **Post date:** [May 31, 2023, 5:08pm UTC](https://community.amazonquick.com/t/quicksight-dynamic-function-lookup-id/1291/5 "2023-05-31T17:08:06Z")
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? > [@Hierarchy - Parent, child and Path](https://community.amazonquick.com/t/hierarchy-parent-child-and-path/14058): > > Helllo everyone, I hope you are doing well. I need help creating a Parent/Child hierarchy and I am unsure what is the best way to do it. I have tried to use: Substring / Locate This is my table… [image] This is what I want. Any thoughts? @emilyzhu , @Max , @Ramon_Lopez have you ever tried to create something like that? Thank you