Data Studio
2.1K members online now
2.1K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply
Highlighted

CASE Calculation

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi, I am trying to work out YoY Growth from a large data set. However I have instances where last year is zero thus not able to simple calculation of ((This Year / Last Year)-1). 

 

Therefore I was trying to use the Case function however can not get it to work. Below is the formula I am trying to apply however get "Invalid formula."

 

CASE
When LY_Sales = 0
Then "n/a"
Else (TY_Sales/LY_Sales)-1
END

 

 

Any help would be great. 

 

 

 

Thank you. 

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Hemal M
2w

CASE Calculation

Follower ✭ ✭ ☆
# 2
Follower ✭ ✭ ☆

Hi Hemal,

 

A limitation of the calculated fields is that THEN/ELSE clauses cannot contain math. You will need to create a separate calculated field whose formula is "(TY_Sales/LY_Sales)-1" then use that new calculated field in your ELSE statement.

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Hemal M
2w

CASE Calculation

Follower ✭ ✭ ☆
# 2
Follower ✭ ✭ ☆

Hi Hemal,

 

A limitation of the calculated fields is that THEN/ELSE clauses cannot contain math. You will need to create a separate calculated field whose formula is "(TY_Sales/LY_Sales)-1" then use that new calculated field in your ELSE statement.