Window Functions and Visualizations

low_priority
done
reply
(Arie Litovsky) #1

Hello,

I am trying to visualize out a few dimensions that involve window functions (in my particular case, it’s PERCENTILE_CONT(), though this really doesn’t matter) . As you explain in this thread, you can only get window functions to work if you define them as a dimension on a derived table, so that there is no grouping happening.

Example:

view: orders {
   derived_table: {
      sql:  
        SELECT 
          *
       , lag(order_amount,1) OVER( PARTITION BY user_id ORDER BY order_date) previous_order_amount
     FROM orders
   ;;
...
   dimension: previous_order_amount {
     type: number
   }
} 

However, the problem is that I can’t graph more than one of these dimensions on a chart, as is mentioned in this thread.

Is there a way to get around this problem? It feels a bit strange that I can’t just graph a dimension – especially when the documentation mentions that this should be possible.

1 Like

(justin1) #2

Hey Arie!

It sounds like what you’ll want to do is define these fields that involve window_functions as measures instead of dimensions. Doing this should make them visualizable in the way you’re describing. The type of measure is going to be dependent on what you’re doing in the window function, so you’ll want to make sure that the type of measure makes sense with the type of window function. I’d recommend also selecting the user_id dimension to get the grouping right for the query and visualization.

Let us know if this gets you what you’re looking for!

0 Likes

(Arie Litovsky) #3

Hi Justin,

Sorry, no, that’s exactly the issue. I can’t define them as a measure, because doing so causes Looker to add a GROUP BY clause to my query, which breaks it. This is explained in more detail in this article:

One important thing to keep in mind about window functions is that they cannot be placed in a GROUP BY clause. In Looker, dimensions are always placed into the GROUP BY clause. Therefore, you cannot simply place a window function in a dimension. For example, the following LookML will not work properly:

Will result in an error - dimension: value_rank_by_store type: number sql: RANK() OVER(PARTITION BY store ORDER BY order_value DESC)

Thus, it seems that it’s not possible to graph any data coming from a Window function.

The article mentions something about joining it back to your original orders table (so, any base table), but I’m not sure how that would make a difference for graphing – these fields would still have to be dimensions.

1 Like

(justin1) #4

Hi Artie,

Definitely picking up what you’re putting down. It’s true that Looker doesn’t have full support for window functions as measures or dimensions. I’ll definitely pass this feedback along to our product team because this is indeed something we’re currently thinking about.

In the meantime however I do believe there is a way to do what you’re going for. The way to do this would be to define them in the PDT, then create aggregate measures in the view (for instance type sum) based off the values in the PDT, and then join them back. The goal here would be to keep the values the same but convert from dimension to measure for the visualizations sake.

Let me know if this does the trick!

1 Like

(Arie Litovsky) #5

Hi justin,

Thanks, that did the trick. Basically my setup is now:
Have a view (called view_window) which does whatever SQL I want, including window functions, without any dimensions defined on it.

Have a second view (called final_view) which simply does a sql: select * from ${view_window.SQL_TABLE_NAME}.
This view has all the measurements defined on it, which don’t interfere at all with the grouping on the view_window.

0 Likes

(allegra) #6

Hey @arie_litovsky, that looks good! Let us know if you have any other questions!

0 Likes

(Keith Cruz) #7

@Brett_Sauve can you add official documentation on window functions with content from your blog post and include the info here as well?

0 Likes

(Brett Sauve) #8

Hey @keith. Since window functions are a SQL feature - and not a Looker feature - they probably won’t make it into our official documentation. Or at least, not in the near future, as we might consider adding SQL tutorials eventually once our core Looker documentation is exactly where we want it.

1 Like

(Setra Yappi) #9

This is still not solved.

0 Likes

(Izzy) #10

Would love to know what specific issue you’re running into here so I can pass that info along!

The workflow of defining the window functions in a derived table, and referencing those as dimensions or measures should be alright— Are you maybe trying to use window functions directly in the sql: parameter of a dimension or measure? That sometimes works, but as we see above, results in some issues with grouping.

0 Likes

#11

I’d love to be able to use a dynamic NTILE in an explore. For example, I’m investigating the impact of company size on average close rate by bucketing company size by NTILE and I want the ability to:

  1. flip between the number of buckets (e.g. quartile, decile, etc.) based on an input in the explore,
  2. update the Partition By clause based on another field in the explore (e.g. industry, region, etc.), and
  3. apply the NTILE bucketing after the filters are applied (e.g. filter out current customers)

Are any of these currently possible without creating a dimension for each distinct scenario in the derived table?

0 Likes

(Izzy) #12

You’d definitely still need a derived table, but I think you could use a templated filter or parameter to dynamically alter the # of buckets/the partition by.

If you’ve not played with those before, I recommend reading through this doc: https://docs.looker.com/data-modeling/learning-lookml/templated-filters
They’re both just ways to take user input from the front-end (in the form of a filter) and apply that to the SQL being run. You could have a parameter on the frontend called “# buckets” and then, based on the input there, alter the derived table by using a liquid if statement.

Check out some other examples here: Great Use Cases for Parameter Fields

0 Likes