The inability to use custom dimensions in charts

  • 26 February 2022
  • 0 replies

I have a project I am working on where I need to be able to use a custom field as the series, or the dimension that we care about, versus the date and we want to see the total revenue. The custom field is a concatenation of dimensions that are in separate dimensional tables in our snowflake schema database, so the SQL would look something like 

, concat(a.first,b.second) as cust_dim
, sum(revenue)
from main_table m
left join dimension_table_1 a
on <key>
left join dimension_table_2 b
on <key>

This obviously isnt the SQL looker generates. 

For a line graph to work, I have to pivot on the cust_dim. This cannot happen because looker writes a query that fails if I only have the cust_dim in the look. If I include the first and second parts in the look, it works to pull the data, but it fails to be functional as I cannot hide dimensions that are in a pivoted position and if they aren’t in a pivoted position I end up with the least useful result, a date+first+second grid that has mostly null entries. 

If I instead pivot by the date, I can hide first and second and things are… okay. Except that I now have a visualization that demands that the date is a series I want data broken out by and it slaps cust_dim on the x axis. 

I need a graph with revenue on the Y-axis, date on the X-axis and my cust_dim as the series. I have looked all over and cannot find anything that addresses this situation.

This topic has been closed for comments