I'm working on a dashboard using data sourced from Google Search Console.
I want to calculate percentage change in performance over two periods. The basic formula is
(New Value - Old Value) / Old Value
I want to see performance 3 months ago vs performance 3 months from "today".
My SQL equivalent to that is
((IF( Date <= CURRENT_DATE() AND DATE_DIFF(Date, Re-Op Date) >= 1 AND DATE_DIFF(Date, Re-Op Date) <= 90, Url Clicks, 0))
-
(IF( Date <= CURRENT_DATE() AND DATE_DIFF(Date, Re-Op Date) >= -90 AND DATE_DIFF(Date, Re-Op Date) <= 0, Url Clicks, 0)))
/
(IF( Date <= CURRENT_DATE() AND DATE_DIFF(Date, Re-Op Date) >= -90 AND DATE_DIFF(Date, Re-Op Date) <= 0, Url Clicks, 0))
However, the percentages are wrong. I tried troubleshooting by subtracting new - old, and the result is correct. But when I divide by the old value, the output is completely wrong. Why is this happening when the denominator is the same as the old value in the numerator?
LinkedIn
Twitter
