I am trying to recover a value from an inline table similar to how you would do it with a VLOOKUP in Excel, but in a QV dashboard. I believe I cannot use "Lookup" or "ApplyMap" as the calculation cannot be done in the script as the user choice is not predictable. The user makes a selection (customer, timeframe let's say) which creates an averaged value. This value has a corresponding multiplier contained in a two column table, which is the value I want to recover in order to use in a further calculation.
Example, the user selects customer = ABC & date range = 2104 & 2015. This gives (say) a month average of 43 months, which corresponds in an inline table (below) to 7.5%. A subsequent value then needs to be multiplied by this 7.5%.
..
41, 6.5%
42, 7.0%
43, 7.5%
44, 8.0%
45, 8.5%
...
How do I dynamically access such a table from within an object and retrieve the 7.5%?
An "IF" statement is not a possible solution as this would make the overall calculation far too complex and unmanageable i.e. not this type of logic:-
IF month_avg = 42 then use 7.0%
IF month_avg = 43 then use 7.5%
Help appreciated,
Nick
