Looker expression editor _ subtraction by pivot rows (sum by grouped dimensions, then subtract)

[happyday]

Hi,

I am new to Looker, not sure if it is possible using Looker expression editor to achieve this (I cannot use LookerML, it is limited to only IT department in my company).

Please see the screenshot below. The green part is what I made within Looker data table by pivoting; the yellow part is what I want to achieve. So it is a subtraction by Year & Amount Range, then use 2021 result - 2020 result.  

Is it possible to do this without using LookerML but by writing expression formula?  Thank you.

[happyday · Accepted solution]

Hi, never mind, I found a way to do it. Do not use pivot table, but create two measures, then create a table calculation by subtract these two measures . 

[prabhakaran_mai]

You can do the calculation with pivoted fields using Pivot_index function

Example:
pivot_index(${measure}, 1) - pivot_index(${measure}, 2)