Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Table calculation between pivots inside parent pivot

  • 14 March 2019
  • 6 replies
  • 2044 views
Dawid
Userlevel 7
Badge +1

Let’s assume the following structure:


          | category 1 | category 2 | category 3|

          | yes  | no  | yes  |  no | yes | no  |



date1     | val1 | val2| val1 | val2| val1| val2|

date2     | val1 | val2| val1 | val2| val1| val2|

date3     | val1 | val2| val1 | val2| val1| val2|

No what I’m trying to do is have a Table Calculation that does val2/val1 for each category pivot. I have been trying to use pivot_where but so far to no avail…

Dawid

6 replies

B
Userlevel 5
Badge

Dawid, if I’m understanding what you’re trying to accomplish, you’re trying to see variances/growth/some other comparative measure within the categories. I believe this might be better done with three measures rather than pivoting a yesno.


For example,


measure val1 {

type: number

sql: … ;;

filter {

field: yesno_field

value: “yes”

}

}


Then repeat the same with value = “no” for val2,


Then finally you’d have a third measure:


measure compare_vals {

type: number

sql: ${val2}/NULLIF(${val1},0)

value_format_name:percent_0

}


If I’ve understood you correctly, adding these three measures should give you just what you’re describing.

Dawid
Userlevel 7
Badge +1

I would like to know if this is possible to be done using Custom Fields. I’d rather not change model/view/explore for one calculation

Dawid
B
Userlevel 5
Badge

Fair enough. Hope there’s a way; I know I’d be interested in using this as well!

izzy
Userlevel 7
Badge +1

I think Ben’s example plays with custom fields too, right?


You can build filtered measures using a Custom Measure, so you can create a custom measure for val1 and val2 filtered on yes/no. Then, you can use a table calc to divide val2/val1.


I picked a bad yesno dimension with lots of nulls for my example, but see if this structure makes sense:

image


where maxscore-no is a custom measure like this (exclude from ranking was the only yesno i had on hand):


image


and maxscore-yes is the inverse.


I think that is the best/most clear way to do this.


You can also do some pivot_where selection with something like this


pivot_where((${category}="category 1" AND ${yesnodim} = no),${val2/val1}) / pivot_where((${category}="category 1" AND ${yesnodim} = yes),${val2/val1})

and repeat for each one of your categories. The key is that the select_expression in pivot_where must return precisely 1 column. If it returns more than 1, it won’t work, so you have to say category = x AND yesno = x.

GenDemo
Userlevel 4

I have the same question. 
I know one can do this with measures and putting filters in them in the LookML.
But I would also like to know if this is possible in table calculations - for the reason that I would like to teach users to self service and not having to change the underlying model.

I have tries @izzy ‘s pivot where method, with mixed success. It sometimes works, others not. And looker does annoying things when you input ‘AND’ and ‘OR’ functions in to the input window.

I get @Dawid point, Sometimes you want to know something is possible, even if it is not the most economic solution.

 

E

Or do the same thing in 3 seconds in Tableau.

Reply


Terms & Conditions