Hello,
I am trying to go from this:
COL1 COL2
ABC 1
DEF 2
GHI 3
to adding a sum of Col2 to Col3, but so it displays across all rows.
COL1 COL2 COL3
ABC 1 6
DEF 2 6
GHI 3 6
Now, in SQL I can do that no problem:
SELECT *
,(SELECT SUM(Col2) FROM Your_Table) Col3
FROM Your_Table
However, I am not sure how I can accomplish the same without converting my Explore into SQL, which kind of defeats the purpose of having an Explore.
Is it possible to do something like this in a derived table block, with referencing ML objects? Even if I have to convert the explore into a bunch of joined views, that would be better than straight SQL.
In the field list on the left of your Explore click on “Custom Fields”. Then click “New”, and select “Table Calculation”
In the text area add
sum(${Col2})
…give the field a name (“Col3” in your example), add formatting, and then save.
This should give you exactly what you are looking for.
Is there a way to define a Table Calculation like this via LookML?
Depending on your SQL dialect you may be able to use a window function, like:
measure: col2_window_total {
sql: sum(${col2}) over() ;;
}
(works on my Redshift connection)
Yes, a window function like the one explained by Michael will work on Redshift, Bigquery, or Snowflake
Be sure not to include type: sum
in your measure declaration or Looker will output the SQL as sum(sum(col2) over ())
and throw an error.
Depending on your SQL dialect you may be able to use a window function, like:
measure: col2_window_total {
sql: sum(${col2}) over() ;;
}
(works on my Redshift connection)
Thank you! This is exactly what I needed. I didn’t know that sum() over() was posible in Look ML.
Hi @swan @michael_zearn @Rodrigo_Pizzano, thank you for your answer! This helped me to get the column percent of total by each category.