# Table calculation between pivots inside parent pivot

(Dawid) #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…

(Ben Silverstein) #2

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) #3

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

(Ben Silverstein) #4

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

(Izzy) #5

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.