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)

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.