RETIRED: Table Calculations (3.18 Labs Feature)


(Abby West) #1

This article has been replaced by this Looker Docs page.


Using offset and pivot_offset in table calculations (3.18+)
Cumulative Sum in MySQL
Creating a percent of total across rows with table calculations
Creating a running total down columns with table calculations
Calculating percent of previous and percent change with table calculations
Creating a column percent of total with table calculations
In what cases does Looker stream results?
[RETIRED]Creating Reference Lines for Charts through the Visualization Editor (3.18+)
Looker 3.20 Release Notes
Looker 3.18 Release Notes
(Adam Rhuberg) #2

A common use case for these calculations within our team are period-over-period calcs using the offset function. Is there a way to format these values yet so that numbers can be displayed as percent values or dollar values? (The rounding piece is nice, but not quite sufficient for this purpose.)


(Abby West) #3

We do plan to offer a more robust set of formatting options for table calculations in the future, but at this point you could present data as dollars or percents by using the CONCAT function and the desired symbol. Syntax is discussed here. This is not as full featured as true formatting, but may suffice in the interim. Hope that helps!


(Adam Rhuberg) #4

Thanks, thatā€™s actually a decent workaround.


(Thomas Vo) #5

Thanks for this great feature! We are already encouraging staff to do more Excel-like functions straight inside of Looker.


#6

Weā€™ve now added formatting via value_format to table calculations in version 3.22! This version will be released over the next couple weeks. Read more about how to use this feature here.


(Gil Margolin) #7
  1. Is there a way to only show the calculated field in a visualization.
  2. Is there a way to add the calculation into a pivot as a row total and not under each pivot option.

#8

The best way to only display a calculated field in a visualization is to hide the other series by clicking their name in the legend. This will not hide them permanently, but it will hide them from view.

There isnā€™t currently a way to create a table calculation that shows up only at the end of all pivoted values, but I will pass this on to the product team. Table calculations are still in beta, so we are looking for ways to improve them!


(Lawrence) #9

Iā€™ve only been using Looker for a few weeks but this is a cool feature. Thank you for your hard work! Bringing up a feature that Tableau has, is there a way to do table calculations like running totals or percent of column/row totals?


#10

Hey Lawrence! Great to hear youā€™re loving table calcs :smiley_cat:

We have a running_total() function in table calcs that makes calculating running totals pretty quick. We have some more about using that here!

For calculating percent of totals, we have a couple posts for doing them down columns and across rows.


(Lawrence) #11

Hi Lindsey,

Does Looker have any integration with R?

I donā€™t have access to this page that might hold some answers: https://discourse.looker.com/t/setting-up-the-lookr-library/199


#12

Is there any way to get the table calculation to display in the Single Value visualization? Currently, it displays the measure that I have chosen, and I do not see any way to customize what gets displayed as the single value.


(Abby West) #13

Hi @ebrakke -
As of Release 3.26 (which will roll out to customers over the next few weeks), you will be able to hide fields in a table by clicking on the gear next to that field in the data table and selecting ā€œHide From Visualization.ā€

If you do that for any field that is not your table calculation, your table calculation will populate the single value visualization.


(Robert Ellman) #14

Abby,

Will that also be available for row totals? Itā€™s a constant bit of friction when creating Pie charts (or donut multiples) to go in and exclude the totals which otherwise take precisely 50% of the visualization.


#15

@abbywest Perfect, thanks!


(Abby West) #16

@Robert_Ellman - Unfortunately no, right now this will only work for fields from the field picker or table calculations.


(Robert Ellman) #17

Iā€™ll keep my fingers crossed for 3.28 :slight_smile:


(Sean MacRae) #18

Hi @abbywest,

Iā€™m wondering if there is a way to apply table calculations column-wise or row-wise. Currently, my table calculations are applied to the entire results dataset, for example:

${users.count} / sum(${users.count}),

uses all ā€˜users.countā€™ cells in the calc, however if I have a pivot table and want to show the percentage relative to each row (in lieu of relative to the entire table) then the table calculation would need to group by one of the pivot dimensions.

I hope that makes sense, let me know if I can clarify anything.


(Abby West) #19

Hi @macrae - for rows, you should be able to use offset and for columns, some combination of pivot_index and/or pivot_offset may suffice.

Here is documentation on offsets and documentation on pivot_index.

I hope those help!


(Chris) #20

Thanks for the info! Super helpful