YTD Calculation

ColinHaze

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

ColemenWilson

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.

ColemenWilson

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.

ColinHaze

In the ETL version, Would I have to use a group by tile and then join that back to the data set?

marcel_luthi

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.