Creating numeric parameters to adjust calculations in the dashboards

[joshsp]

Hi, we have an efficiency metric in Looker. I want to be able to multiply that efficiency metric by a numeric parameter for capacity to see what our endurance is. 

An example with a vehicle would be:
- the sum of distance traveled in a specific scenario is 100 distance units
- the sum of fuel consumed in that scenario is 20 consumed units
- the efficiency of the vehicle is 5 distance units per fuel unit in that scenario (distance/ consumption)
- we have 10 scenarios shown in the data

Now I want to have a parameter on my dashboard that allows the user to determine fuel capacity
- user enters 40 fuel units as the fuel capacity into the parameter
- a new column now shows that we get 200 distance units (distance/consumption*{capacity parameter})
- now we can easily compare scenarios and find the optimal scenario for different capacities

It does not appear Looker allows any way to pull parameters out from the filter-level and into the actual calculation. Ideally I'd have a new column that is the numeric parameter that I can just multiply against the distance and consumption columns, then I can see the projected distance, and I can change it easily.

Is there no way to get parameters into the table calculations? I tried a few different methods with casting the liquid parameters, etc., but it appears that it only works with conditional statements to filter data out. 

[GavinW · Accepted solution]

It's not ideal, but you can inject your value into the actual LookML code using the parameter parameter (or parameter squared, maybe?). You would just create a 'capacity' parameter, and add 'allowed values' from say 1 to 50 or whatever, and the user on the front end can pick whatever value they want from the dropdown. To model it, you would have something like an 'endurance' field that would pull in distance, efficiency and whatever else and drop in the capacity parameter wherever it makes mathematical sense in your calculation.

If distance and efficiency are measures, it might look something like:

measure: endurance {
type: number
sql: ${distance} / (${consumption} * {% parameter capacity %});;
}

Note that you should define your parameter type as 'unquoted' if you intend to drop it into your SQL like this, otherwise it will be added to your SQL as a string.

[joshsp]

I had something in mind like this, but I couldn't figure out how to actually execute on it. I got this to work though - thank you.

I did not put any "allowed values" in the parameter script, and then I changed the filter on the dashboard to "Advanced" where you can type in a value. This works, but you have to ignore the modal saying that there are no matching values for what you entered. Is there any way to allow all values so that you don't get this dropdown see below? 

It still works even though it shows the warning, so it's just confusing. I will have to write some error handling (putting in non-numeric values) as well.