Percent change and difference

Hello!
I created a custom filter to calculate transactions in last month and same month last year (year over year analysis).
I would like to calculate difference between those two values and percent change. Is there any way I can add a new table calculation or measure to calculate these values?
I can calculate sum using sum function in table calculation. Want to calculate difference in similar manner.

0 5 7,763
5 REPLIES 5

mprogano
Participant III

You can! Using table calculations 🙂

Hi @mprogano!
Thank you for your quick response 🙂 This works perfectly fine!

i am new to looker. used the below to get the percent of previous. 

measure: sale_qty_change {
    type: number
    sql:  ${sale_qty} / offset(${sale_qty}, -1);;
  }

giving me an error in the explore:

The Google BigQuery Standard SQL database encountered an error while running this query.

Query execution failed: - OFFSET is not a function. It can only be used for array element access using array[OFFSET(position)] at [7:64]

302cb4eb-1feb-481f-bc40-2722793b0c5c.png

Hello, I have the same problem, how do you solve it?

Hi @av11@DavidSolorzano  - the syntax that was linked above is specific for table calculations, not for LookML. That's the cause of your error. Table calculations will take place after the SQL executes, that's why it is able to reference the results for calculations.

If you needed to do a similar calculation in SQL, I'd encourage you to check out LAG/LEAD functions for your SQL dialect. I've not implemented one of these through LookML, and likely wouldn't want to, because it's dependent on data sorting.

Top Labels in this Space
Top Solution Authors