MTD & YTD Toggle

Data_Devon · March 31

Hi experts,

I'm trying to create a toggle that allows users to view Month to Date or Year to Date data.

Current state is a bar graph with years represented by different bars - 2025, 2024, and 2023. Each of these is a dedicated column in the dataset so it's just a SUM(2025), SUM(2024), and SUM(2023) for each series.

The x-axis is then the MONTH(`Date`)

https://community-forums.domo.com/main/discussion/67412/mtd-and-ytd-toggle

Because of this quirky set up, the date filters that are native to a dashboard don't work since the months are still on the X axis:

I tried the solution in the linked thread but that didn't get me where I need to go either.

Any ideas for a solution?

DavidChurchman · March 31

It would be helpful to have a dummy sample of your data, because you're throwing me off by saying you have a column for 2023, 2024, and 2025 values. That sounds like a wide dataset, but then you have a single date column, which sounds like long data. I'm going to assume your data is long and that those year columns are just null for all dates that don't match the year for their column header.

I'm also having trouble understand which data would be include in a MTD calculation that wouldn't also be included in a YTD , so I'd be curious to see how you calculated your helper column in your ETL and some dummy examples of when it would be 0, 1, or 2.

But, if that 'MTD and YTD Helper' column is correctly identifying rows as 1=MTD and 2=YTD, then your approach should work, except you don't want to use >=1 before =2 in your CASE statements, because 2 is great than 1, so 2 will already be captured in that first "WHEN" and will never be captured by the =2. Both 1s and 2s will be coded to YTD.

But, assuming your data is long, then @brycec is correct that a variable would be the best approach. Filters are good for controlling what data to display, but variables allow you to control how the data is displayed. If you want to give people control over whether it's a MTD calculation or a YTD calculation, it would follow this basic process:

Create a variable with two options: YTD and MTD
Create a BeastMode referencing your variable that switches between the calculations for YTD and MTD. These Period-over-period calculations are a bit finicky, but I'm assuming you've already figured this bit out based on your screen shot. It would be something like this:
1. CASE WHEN ytd_mtd_variable = 'YTD' then [YTD BEASTMODE] WHEN ytd_mtd_variable = 'MTD' then [MTD BEASTMODE] END
On your dashboard add a "control". Once you add a card to your dashboard that has a beastmode that references a variable, you now have the option to add a Variable control. It looks like a filter, but isn't. It gives user the ability to switch the value of the variable.

The hardest part is step 2a, getting the MTD and YTD calculations correct. I would recommend starting there, before setting up your variable. Are you able to get them to calculate correctly before adding the variable control?

If none of that makes sense, then I recommend creating a dummy sample of your data to share, as well as a mocked up example of what you would hope the output to be from your card. Sometimes the process of putting that together unlocks something, but either way, it makes the quality of answers you get here much higher.

DashboardDude · April 1

@Data_Devon

What if you used variables to change the different axis and comparisons? Here's an example: https://youtu.be/2xuLwBGM9U8

DavidChurchman · April 1

With your sample data, I think this does what you were asking for:

Create a control variable:

Create a beastmode reference your variable (when YTD, finding the cumulative sum by year, else find the regular sum):

Add the variable control and you should be able to toggle between MTD and YTD calculations:

DavidChurchman · March 31

It would be helpful to have a dummy sample of your data, because you're throwing me off by saying you have a column for 2023, 2024, and 2025 values. That sounds like a wide dataset, but then you have a single date column, which sounds like long data. I'm going to assume your data is long and that those year columns are just null for all dates that don't match the year for their column header.

I'm also having trouble understand which data would be include in a MTD calculation that wouldn't also be included in a YTD , so I'd be curious to see how you calculated your helper column in your ETL and some dummy examples of when it would be 0, 1, or 2.

But, if that 'MTD and YTD Helper' column is correctly identifying rows as 1=MTD and 2=YTD, then your approach should work, except you don't want to use >=1 before =2 in your CASE statements, because 2 is great than 1, so 2 will already be captured in that first "WHEN" and will never be captured by the =2. Both 1s and 2s will be coded to YTD.

But, assuming your data is long, then @brycec is correct that a variable would be the best approach. Filters are good for controlling what data to display, but variables allow you to control how the data is displayed. If you want to give people control over whether it's a MTD calculation or a YTD calculation, it would follow this basic process:

Create a variable with two options: YTD and MTD
Create a BeastMode referencing your variable that switches between the calculations for YTD and MTD. These Period-over-period calculations are a bit finicky, but I'm assuming you've already figured this bit out based on your screen shot. It would be something like this:
1. CASE WHEN ytd_mtd_variable = 'YTD' then [YTD BEASTMODE] WHEN ytd_mtd_variable = 'MTD' then [MTD BEASTMODE] END
On your dashboard add a "control". Once you add a card to your dashboard that has a beastmode that references a variable, you now have the option to add a Variable control. It looks like a filter, but isn't. It gives user the ability to switch the value of the variable.

The hardest part is step 2a, getting the MTD and YTD calculations correct. I would recommend starting there, before setting up your variable. Are you able to get them to calculate correctly before adding the variable control?

If none of that makes sense, then I recommend creating a dummy sample of your data to share, as well as a mocked up example of what you would hope the output to be from your card. Sometimes the process of putting that together unlocks something, but either way, it makes the quality of answers you get here much higher.

Data_Devon · March 31

@DavidChurchman - Thanks for the thorough response. I'm afraid to share my data - you'll see what a mess it is.

I've attached a sample. You'll see the following:

Total - The total for the given procedure, by the given producer, on the given day, in the given location

Month - calculated from the Date column

Year - calculated from the Date column

Actual or Budget - this could be the crux. The budget values are aggregated up to the monthly level so each month has just one day budgeted - the first of the month - that has the entire month's total volume. So when I SUM(Budget) for March, It'll compare correctly to SUM(Actual) for March.

Procedure, Location, Producer - these are redacted but you'll get the point. Multiple procedures across multiple locations and multipled producers.

To get the different bars (as presented in my screenshot) I do a CASE WHEN :

CASE WHEN YEAR= 2025 THEN '2025 TOTAL'
WHEN YEAR = 2024 THEN '2024 TOTAL'
WHEN YEAR = 2023 THEN '2023 TOTAL'
WHEN YEAR = 2025 AND Actual or Budget = 'Budget' THEN '2025 BUDGET'

So the data is widened within beastmodes in Analyzer. The cards have Month on the X Axis, and the Y Axis is each Year - SUM(2025 Total), SUM(2024 Total), SUM(2025 Budget) and SUM(2023 Total)

Each card defaults to this:

This is helpful to see the full year, but users can't easily see the current year's total or just one single month.

The screenshot above is the default. The hope is that a user can select YTD and get a view like this:

And then select Month to date to change the X-axis to Month to Date (the view would be the same.)

Phew. Thanks in advance for your help and getting me this far.

Redacted - Procedure Sample for Domo Help.xlsx

DashboardDude · April 1

@Data_Devon

What if you used variables to change the different axis and comparisons? Here's an example: https://youtu.be/2xuLwBGM9U8

Data_Devon · April 1

@DashboardDude - good stuff. That's what I was leaning towards through my continued research.

Thanks Chef 🧑‍🍳

DashboardDude · April 1

@Data_Devon it's YES CHEF!!!! Just kidding. Happy to help

DavidChurchman · April 1

With your sample data, I think this does what you were asking for:

Create a control variable:

Create a beastmode reference your variable (when YTD, finding the cumulative sum by year, else find the regular sum):

Add the variable control and you should be able to toggle between MTD and YTD calculations:

Data_Devon · April 1

Thanks @DavidChurchman - I think I'll be able to combine your solution with DashboardDude's and get to exactly where I need to go.

Thankful as always to have access to this community!

MTD & YTD Toggle

Best Answers

Answers

Categories