In many SQL client GUIs you can create a query parameter, which can turn an element of a WHERE statement into a variable, and the user is prompted to enter a value so that the query can run based on that value.
Here is my idea I am trying to solve for:
I have a relatively complex formula that uses some product sales metrics and some user-defined values to come up with an expected cost. For simplicity sake, let’s say it just looks like this:
Cost = metric1 x U, where U is some number
Is there a way to turn a dimension within a derived table into a variable based on the value of a filter?
I’m thinking I could create a derived table that generates a series, but I don’t have any ideas on how to join such a table to any other table without creating a cartesian product, which in this case could be too many rows.
Any ideas? I’m an Accountant that knows enough SQL to be dangerous, so please forgive me if the answer is simple.
I believe that you can accomplish this using templated filters to create a derived table with a dynamic where clause which you can use to limit the table to only have the row(s) associated with U.
Then we can join this derived table back to the explore using a cross join, which should be okay because the resulting table should be pretty small.
Let me know if that makes sense!