Variable to store values in lookml

[trprado]

Hi,
I need to create a variable that stores a fixed value based on a measure. An example is the total orders I have in a date range.

My purpose is using this variable to calculate other costs, but I don’t know how to create this value without modifying the variable when I use filters in explorer.

[Dawid]

Can you show us an example?

[shiggins]

Without much context, what comes to mind is using a Native Derived Table to calculate the variable (orders within a range) that’s joined back into your main Explore and leveraged (as a Dimension) for calculating Costs.

[trprado]

Is it possible using NDT and creates a query like WITH of the SQL?

A temporary solution that I used to solve my problem is:

WITH y AS (
    SELECT COUNT(*) AS count
    FROM x
    WHERE (my filters)
)
SELECT x.id, y.count
FROM x
CROSS JOIN y
WHERE (my filters)

And create a view from this result.

[shiggins]

Yes, a Native Derived Table that isn’t persisted will produce a CTE like that. As will a regular derived table.
One of the major benefits of using a NDT vs a regular derived table is you can use bind_all_filters which will mitigate the need to create Templated Filters for each of the statements in your WHERE clause, which is particularly pertinent in your use case where your “my filters” will likely change over time.