LinkedIn
Twitter
Hello fellow Lookers,
I spoke with a customer the other day who wanted to compare where their web traffic was coming from. What they wanted to do was compare 2nd place against 1st place and then all the others against 2nd place. Something like the query below:
In traditional programming languages, you use if/else statements when you want to check the condition of some variable.
if(x = 1){
code
}
else if(x = 2){
more code
}
else{
even more code
}
With Looker’s table calcs, that same behavior can be applied like so:
if(${x} = 1, some code, if(${x} = 2, more code, even more code))
To accomplish what the user was trying to do, we took advantage of this if/else structure that we can do with Table Calcs.
Here is what we came up with:
if(row() = 1, 0, if(row() > 2, ${order_items.count} / offset(${order_items}, 2 - row()), ${order_items.count} / offset(${order_items.count}, 1 - row())))
or like this in the table calc modal:
There are a few other operators that we took advantage of, the row() and offset() functions. More on those functions here. Since Looker doesn’t support accessing individual cell values, we can use a nice workaround with the row() function. So, to access Google’s count, we would write 1 - row(). To access Yahoo’s, we write it like 2 - row(). Basically, if we want to access the nth row’s cell, we use n - row().
When used with the offset function, we will always pull the first row or second row’s count measure. This produced the results below!
