How to use filters to calculate MTD, QTD & YTD using 3 different cards?

Options
Member

We need 3 filters for Month, Quarter & Year and using the same we want to drive the cards to be show MTD, QTD & YTD calculations on same page. Please note MTD, QTD & YTD as 3 different Bar Graph charts.

Is there any easy way to achievr this? Currently we are exploring Parameters/variables.

Best Regards,

Tejal

• Coach
Options

Interesting that so many suggest variables or parameters. I don't even think I'm using variables for anything.

I have posted examples previously with the way I do this. I create an ETL with aggregates that include fields for CY, PY, P2Y, etc. The same aggregate tiles include calculations for MTD, QTD, etc. For things like MTD or QTD, you just add more criteria for whether your date field is whatever quarter or month, or less than current month, etc.

CY:
sum(
case when YEAR(`date`) = YEAR(CURRENT_DATE())
and `date` <= CURRENT_DATE()
then `amount` else 0 end
)

PY:
sum(
case when YEAR(`date`) = YEAR(CURRENT_DATE())-1
and `date` <= DATE_ADD(CURRENT_DATE(),-365)
then `amount` else 0 end
)

or

sum(
case when YEAR(`date`) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and `date` <= DATE_ADD(CURRENT_DATE(),-365)
then `amount` else 0 end
)

** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **

• Coach
Options

I think you could do all of this with no BeastModes or Variables.

A variable would be useful if you were trying to do this all with one card, with the ability to switch the metric from MTD, QTD, YTD, but you want to use three different cards, so you can easily define the "graph by" on each card using the date options.

I also don't think you need three filters. Based on my reading of your PDF, all three filters you describe are basically saying the start date you want to be used. The simplest implementation would be a "range selector", card where users can adjust the date range to be displayed.

Also based on your PDF, it seems like you want to compare to the prior year. Use a period-over-period card, and you should be able to do that, as well as define whether it's a by-month, by-quarter or by-year card.

Please 💡/💖/👍/😊 this post if you read it and found it helpful.

• Coach
Options

@TejalGovila - I would say that variables are probably your best bet. You would want to define the logic for the date ranges and then use that variable inside of a case statement. You can then place the variable on to a dashboard as a control, and let users toggle.

David Cunningham

** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**

• Domo Employee
Options

Hi @TejalGovila , Here is a good example page of a few types of variables used to show different time slices.

Example Period over period variable page:

• Member
Options

Thank you David & Thank you JedP.
Howevere what I am looking for is 3 filers ate top Month, Quarter & year and same way 3 cards on the page named MTD, QTD & YTD and the details should show as per attached.

• Coach
Options

Interesting that so many suggest variables or parameters. I don't even think I'm using variables for anything.

I have posted examples previously with the way I do this. I create an ETL with aggregates that include fields for CY, PY, P2Y, etc. The same aggregate tiles include calculations for MTD, QTD, etc. For things like MTD or QTD, you just add more criteria for whether your date field is whatever quarter or month, or less than current month, etc.

CY:
sum(
case when YEAR(`date`) = YEAR(CURRENT_DATE())
and `date` <= CURRENT_DATE()
then `amount` else 0 end
)

PY:
sum(
case when YEAR(`date`) = YEAR(CURRENT_DATE())-1
and `date` <= DATE_ADD(CURRENT_DATE(),-365)
then `amount` else 0 end
)

or

sum(
case when YEAR(`date`) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and `date` <= DATE_ADD(CURRENT_DATE(),-365)
then `amount` else 0 end
)

** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **

• Coach
Options

I think you could do all of this with no BeastModes or Variables.

A variable would be useful if you were trying to do this all with one card, with the ability to switch the metric from MTD, QTD, YTD, but you want to use three different cards, so you can easily define the "graph by" on each card using the date options.

I also don't think you need three filters. Based on my reading of your PDF, all three filters you describe are basically saying the start date you want to be used. The simplest implementation would be a "range selector", card where users can adjust the date range to be displayed.

Also based on your PDF, it seems like you want to compare to the prior year. Use a period-over-period card, and you should be able to do that, as well as define whether it's a by-month, by-quarter or by-year card.

Please 💡/💖/👍/😊 this post if you read it and found it helpful.