YTD Calculation
Hi,
I am trying to calculate a YTD number. I have a card that is set to Current Month and im trying to add a column that includes a YTD number. However, I seem to just keep getting this months numbers in that column. I have tried a variety of beastmodes but none seem to work.
Ive tried
SUM(CASE
WHEN Fiscal Months
>= '07-2023' AND Fiscal Months
< '07-2024' THEN Total Miles
END)
Ive tried partitioning it by year(fiscal Month)
Ive tried adding a column in my dataset called YTD by taking the rows that fall between 07-2023 and 07-2024 and calling it 'Current Year' Then writing a beastmode
SUM(CASE WHEN YTD
= 'Current Year' THEN Total Miles
END)
and filtering the card for just current year. Still receive only this months data.
Is the only way to solve this issue to change the date on the card to between 07-2023 and 07-2024 then writing beastmodes that basically sum(Case when fiscal months = month(curdate()) then total miles END
Best Answer
-
Easiest thing to do is have 2 beastmodes: Current month and YTD. When the card is set to current month, only rows that match that criteria will be shown. An alternative would be to use Magic ETL to calculate the YTD value and populate it for every row in the dataset. Then, use that field in the card and just set the field aggregation to MAX.
If I solved your problem, please select "yes" above
0
Answers
-
Easiest thing to do is have 2 beastmodes: Current month and YTD. When the card is set to current month, only rows that match that criteria will be shown. An alternative would be to use Magic ETL to calculate the YTD value and populate it for every row in the dataset. Then, use that field in the card and just set the field aggregation to MAX.
If I solved your problem, please select "yes" above
0 -
In the ETL version, Would I have to use a group by tile and then join that back to the data set?
0 -
For ETL yes, but you need to ensure you group at the level of granularity that your users will be able to play with (so that totals are properly adjusted when filters are applied to the card, otherwise you'll have a fixed value that won't update as filters are applied). The beast mode route, if the card is meant to be responsive to filters, might be a simpler approach (besides avoiding you having to have 2 datasets with basically the same data and just 1 additional column).
As you have stated, the
date range
filter in the card will always be kind of troublesome and most times when I have to show multiple TO DATE values, I leave the date range to be All Time and handle which entries to totalize/use as part of the beast mode calculation with a CASE statement.You could also use 2 cards with diferent
date ranges
and put them side by side, just as a third alternative.0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 600 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 685 Beast Mode
- 43 App Studio
- 38 Variables
- 655 Automate
- 170 Apps
- 438 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 383 Distribute
- 110 Domo Everywhere
- 267 Scheduled Reports
- 6 Software Integrations
- 111 Manage
- 108 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive