Question

Table calculation between pivots inside parent pivot

• 6 replies
• 2044 views

Userlevel 7
+1
• Member
• 785 replies

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…

6 replies

Userlevel 5

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.

Userlevel 7
+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

Userlevel 5

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

Userlevel 7
+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:

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

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.

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.

Or do the same thing in 3 seconds in Tableau.