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

TejalGovila

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

ArborRose

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
)

DavidChurchman

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.

david_cunningham

@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.

JedP

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: https://embed.domo.com/embed/pages/8qNll

TejalGovila

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.

Expected Behaviour - Sheet1.pdf

ArborRose

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
)

DavidChurchman

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.

TejalGovila

Thank you all but still this won't solve my problem :(