SQL for calc fields that sum (data is flattened) are dynamic -- based on current year

AngelaO415

Hi there: I need help creating these calculated fields (in Beast Model) where they change based on current year. I created this in EXCEL but am still learning SQL - any help would be much appreciated!

calculated fields
cy	Sum `rtfs_amount` for each DISTINCT `Base Request ID`where `fsa_spending_year` is current year
cy+1	Sum `rtfs_amount` for each DISTINCT `Base Request ID`where `fsa_spending_year` is current year+1
cy+2	Sum `rtfs_amount` for each DISTINCT `Base Request ID`where `fsa_spending_year` is current year + 2
cy+3	Sum `rtfs_amount` for each DISTINCT `Base Request ID`where `fsa_spending_year` is current year +3

Here is SAMPLE data I am working with:

request_transactions_organization_payee_id	Base Request ID	rtfs_amount	fsa_spending_year
700	1	$22,500.00	2023
700	1	$22,500.00	2024
700	1	$22,500.00	2025
800	2	$1,000.00	2023
800	2	$1,000.00	2024
800	2	$1,000.00	2025
800	2	$1,000.00	2026
800	2	$1,000.00	2027
900	3	$200.00	2023
900	3	$200.00	2024
800	4	$350.00	2024
700	5	$1,450.00	2024
700	5	$1,450.00	2025
700	5	$1,450.00	2026
700	5	$1,450.00	2027

sample data - domo.xlsx

ArborRose

You don't need SQL. In beast mode, make a calculation such as…

CY:

CASE WHEN year(`fsa_spending_year`)=year(curdate()) THEN `rfs_amount` ELSE 0 END

For previous year, subtract 365 days. Two years previous, 730 days. Etc.

PY:

CASE WHEN year(`fsa_spending_year`) = year(date_sub(curdate(), interval 365 day)) THEN rfs_amount ELSE 0 END

david_cunningham

It's because you're comparing an integer YEAR(CURDATE()) to a string (fsa_spending_year).

Try this instead

CASE WHEN CAST(`fsa_spending_year` AS INT)=year(curdate()) THEN rtfs_amount ELSE 0 END

ArborRose

You don't need SQL. In beast mode, make a calculation such as…

CY:

CASE WHEN year(`fsa_spending_year`)=year(curdate()) THEN `rfs_amount` ELSE 0 END

For previous year, subtract 365 days. Two years previous, 730 days. Etc.

PY:

CASE WHEN year(`fsa_spending_year`) = year(date_sub(curdate(), interval 365 day)) THEN rfs_amount ELSE 0 END

AngelaO415

Thanks @ArborRose - I see conceptually how it should work and I appreciate how you did that! How do I add in there that I want to SUM the rtfs_amount by fsa_spending_year for each Base Request ID. I realize I gave you too perfect of a data sample. There could be cases where the rtfs_amount exist 2-3 times in a given year so I want to add those since they represent diff sources of money within that year.

request_transactions_organization_payee_id	Base Request ID	rtfs_amount	fsa_spending_year
700	1	$22,500.00	2024
700	1	$22,500.00	2024
700	1	$22,500.00	2025
800	2	$1,000.00	2023
800	2	$1,000.00	2024
800	2	$1,000.00	2025
800	2	$1,000.00	2026
800	2	$1,000.00	2027
900	3	$200.00	2023
900	3	$200.00	2024
800	4	$350.00	2024
700	5	$1,450.00	2024
700	5	$1,450.00	2024
700	5	$1,450.00	2025
700	5	$1,450.00	2025

AngelaO415

I also meant to ask: fsa_spending_year is formatted as string (abc) - do I have to convert that to integer in order to have it match to current year? Sorry, I am such a newbie.

ArborRose

Typically, you would do it on a date field. But I believe it will convert a string value using

YEAR(`fsa_spending_year`)

You can use aggregate functions like COUNT and SUM inside beast mode calculations. Just wrap the whole case statement in SUM(…).

It's okay to be new. I haven't been on here as long as the ancient ones. There are a lot of people here to help, just ask.

AngelaO415

For some reason, the Beast Mode didn't work. This is the what I used:

CASE WHEN year(fsa_spending_year)=year(curdate()) THEN rtfs_amount ELSE 0 END

This became my output. I would have expected 150000 to appear in the test(cy payments) column.

david_cunningham

It's because you're comparing an integer YEAR(CURDATE()) to a string (fsa_spending_year).

Try this instead

CASE WHEN CAST(`fsa_spending_year` AS INT)=year(curdate()) THEN rtfs_amount ELSE 0 END