Custom calculation for Y-1 sales figures

[M_Zander96]

Hi, I'm trying to create a dashboard showing this years (Y) sales figures compared with last years (Y-1) in order to show YoY growth. I used gemini to get the code, however it comes up as expression incomplete. Please can someone help identify and correct the errors? Thanks!

Gemini suggested this code:

SUM(
CASE
WHEN YEAR(${salesforce_opportunities.close_date}) = YEAR(DATE_ADD(${salesforce_opportunities.close_year}, INTERVAL -1 YEAR))
THEN ${salesforce_opportunities.one_year_total_price_usd}
ELSE 0
END
)

with:
- ${salesforce_opportunities.close_date being the dimension for the date an opportunity was closed

- ${salesforce_opportunities.close_year} being the dimension for the year an opportunity was closed

- ${salesforce_opportunities.one_year_total_price_usd} being the dimension for the USD amount of the opportunity

[GavinW]

If you are writing this in Looker (rather than Studio) then we don't write SQL code in table calculations. Table calcs are written using Looker Expressions, and you can see the docs here.

Having said that, the easiest way to do a YoY comparison on the front end (rather than in LookML) is to pivot your results on the years you are interested in.