Using lists in table calculations (3.36+)


#1

As of Looker 3.36, we have introduced the concept of lists in table calculations. Lists allow you to group values together and then perform a common calculation across them. Read using pivot_row lists here.

The list function allows you to create your own list. For example, let’s say I I have this table:

I want to know create a column that gives me the max of either the value of Orders Count, or the average of these 5 rows. I can do this by first creating a list of the average and the Orders Count value:

list(mean(${orders.count}),${orders.count})

This will list those two values like so:

Then, I can take the max of that list, like so:

max(list(mean(${orders.count}),${orders.count}))

This will create a list of the average and whatever value is in each row for Orders Count. Then it takes the maximum of those two values.

Lists are particularly useful for aggregating across values that aren’t physically in your table. This could be a hard-coded value (e.g. 14) or a value derived from the data in the table (e.g. mean(${orders.count}).`


Rolling average using offset_list in table calculations (3.36+)
Looker 3.36 Release Notes
(Nick Erdenberger) #2

This is incredibly powerful.

Glad I found this, thanks guys.


(Sofia Wright) #3

Hi, this is a very useful function!

I am wondering if there is a way to limit what is included in a list based on a condition; say only include values that are greater than 0.

Thanks!

EDIT: I realized my question makes the most sense in the specific context of pivot_row(), which is what I am working with and discovered the list function from there.


(Max Corbin) #4

@sofiaw, I don’t believe this is possible. Whenever I need to pull out specific parts of my data, I try to use something like pivot_where, but that will pull out individual values from specific pivoted columns.

Maybe I can help with an alternative approach? What are you trying to do?


(Johan De Beurs) #5

Is there any way to reverse the order of a list in table calculations?


(Aleksandrs Vedernikovs) #6

Hi @Johan,

Is there a reason why would you want to do that? We do not have reverse() function, but I would like to understand the issue behind this if you know what I mean.

Best,

Sasha


(Johan De Beurs) #7

Was playing around with trying to hack window functions via lists within table calcs and using a lookup() can give me the start of a partition, but if I want to get to the end of a partition it is messy. A lookup(value, reverse(list)) could have been tidier to achieve this


(Aleksandrs Vedernikovs) #8

Hi @Johan,

Ok got you. Well as I have mentioned before we do not have reverse() function, but I would be happy to pass along this to our product team. As for windows() we recommend to use them in the derive table syntax. More info you can find here..

Best,

Sasha


#9

Is there any way to count distinct items in a list created from the table calculations?


(jeffrey.martinez) #10

Great question @cvax!

For now this is not possible. But its an idea I can gladly pass along to the product team.

Take care,

Jeff


#11

Hmmm @jeffrey.martinez, do you have any suggestions for how I can achieve what I need then?

https://i.imgur.com/83b6Sg9.png

I am trying to get a running distinct count of accounts that have repurchased 1-day out since their first purchase, 2-days out, 3-days out, etc. So at day1, there are four accounts. At day2, it would be the original four plus three new accounts. I am trying to get a cumulative distinct count of repurchasing accounts since day1 to day-x.


(jeffrey.martinez) #12

Thanks for sending that over @cvax,

We may be on to something here, and perhaps I spoke too soon, or at least in this case we have a work through. It looks like your “Repurchase Test Accounts” column is something we can perform a table calculation on. Try doing a “count_distinct” table calculation on Repurchase Test Accounts, then a 'running_total" table calculation on that generated column.

I think that should work! Let us know!

Jeff


#13

@jeffrey.martinez Thank you for the reply. Wouldn’t that only give me the distinct counts per “Days Since First Purchase” though? What I am trying to do is have a running distinct count from the beginning.

For example, if account 11111 bought in day1, then bought again in day2, my count should still be 1 because it was the same user on both days.


(jeffrey.martinez) #14

@cvax I gotcha, yes you are right, my advice doesn’t quite do the trick in this case.

For what you are looking for, using lists we would need to compile your list of “Repurchase Test Accounts” into a cumulative running list. Then run a distinct count on that running list. This would require making a table calculation list OF a list, which is not currently possible. Without access to your model, it’s hard to recommend an alternate solution.

Great question though, thank you for clarifying. The original answer still stands, but now we have more depth of a use case to convey to the product team!

Take care,

Jeff