I have a dataset where rows correspond to data collected at different points in time like so:
| metric | date | FY | result |
|---|---|---|---|
| visits | 03/01/2022 | FY23 | 17 |
| visits | 04/01/2022 | FY23 | 21 |
| visits | 05/01/2022 | FY23 | 34 |
| visits | 06/01/2022 | FY24 | 99 |
| visits | 07/01/2022 | FY24 | 11 |
| visits | 08/01/2022 | FY24 | 27 |
I want to add a column that shows the value from the result column that (a) is in the same FY as the current month AND (b) is furthest back
-every metric in the dataset set does not have values 12 months back, some may have 3 or 7 months or 11 months, etc….
desired output:
| metric | date | FY | result | year_ago |
|---|---|---|---|---|
| visits | 03/01/2022 | FY23 | 17 | 17 |
| visits | 04/01/2022 | FY23 | 21 | 17 |
| visits | 05/01/2022 | FY23 | 34 | 17 |
| visits | 06/01/2022 | FY24 | 99 | 99 |
| visits | 07/01/2022 | FY24 | 11 | 99 |
| visits | 08/01/2022 | FY24 | 27 | 99 |
I am trying to use LAG in a case statement to loop through the previous 12 months but it returns 0 instead, not sure if multiple expressions are matching or mismatching strings and integers?
CASE
WHEN result <> 0 AND LAG(FY,1) = FY THEN COALESCE(LAG(result,1) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,2) = FY THEN COALESCE(LAG(result,2) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,3) = FY THEN COALESCE(LAG(result,3) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,4) = FY THEN COALESCE(LAG(result,4) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,5) = FY THEN COALESCE(LAG(result,5) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,6) = FY THEN COALESCE(LAG(result,6) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,7) = FY THEN COALESCE(LAG(result,7) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,8) = FY THEN COALESCE(LAG(result,8) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,9) = FY THEN COALESCE(LAG(result,9) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,10) = FY THEN COALESCE(LAG(result,10) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,11) = FY THEN COALESCE(LAG(result,11) OVER (PARTITION BY date ORDER BY date),0)
ELSE 0
END AS year_ago