Local Variables
SQL Frames provides aggregate fields, analytic fields and calculated fields. Sometimes the final output requires only a calculated field that depends on either an aggregate field and/or an analytic field. For example, showing total revenue and percent of total requires also calculating the total revenue which is an anlaytic field. Since this total revenue is the same for all the rows, it is not desirable to show. This is where local variables come into picture. Let's see an example of both with and without local variables.
% of total
with repeated value
Notice how the 'WW Revenue' is repeated for each row.
with pdf()
The usual workaround to not show the repeating 'WW Revenue' field is
The only drawback with this approach is every time the parent df (gdf above) definition
is changed with new fields, the projection has to be updated as well.
with local variable
Local variables is an experimental feature. If it doesn't work for a specific use case, the
quick workaround is to use the pdf() to project only the desired fields.