SQL for calc fields that sum (data is flattened) are dynamic -- based on current year
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 |
Best Answers
-
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_amountELSE 0 END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0
Answers
-
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_amountELSE 0 END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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
0 -
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.
0 -
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.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
For some reason, the Beast Mode didn't work. This is the what I used:
CASE WHEN year(
fsa_spending_year)=year(curdate()) THENrtfs_amountELSE 0 ENDThis became my output. I would have expected 150000 to appear in the test(cy payments) column.
0 -
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
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive