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

TejalGovila
TejalGovila Member
in Charting

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

Best Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    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! **

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    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.

    image.png

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

    Please accept the answer if it solved your problem.

Answers

  • david_cunningham
    david_cunningham Coach

    @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! ✔️**

  • JedP
    JedP Domo Employee

    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
    TejalGovila Member

    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
    ArborRose Coach
    Answer ✓

    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! **

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    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.

    image.png

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

    Please accept the answer if it solved your problem.

  • TejalGovila
    TejalGovila Member

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

Categories