Selecting Date Range for both YTD and YOY Cards on a Single Page

frwern01
frwern01 🟠
in Ideas Exchange

Does anyone have a way to use the Page Filters to select the date range of 20+ cards where some are Year To Date (YTD) driven and others are Year Over Year (YOY) driven? Our Account Managers (AMs) like the fact that we have pages set up for PowerPoint presentation creation using the PPT Plugin tool. They just select a particular client and refresh the presentation. Easy.

 

Problem is we need to separate the YTD and YOY cards onto different pages if we want the AMs to select their own date range because the YOY cards require this year and last year (or last year and the year before). So if I set the Page filter for 2018 and 2017 the YOY cards will be great but the YTD cards will contain too many rows. There seems to be NO way to dynamically set a YTD card to be sure it uses the MAX(Year) as aggregations ar NOT allowed in the FILTERS section of a card.

 

Much of this has to do continuing Domo maintenance as well as ease of use for our AMs.

 

Thanx

0
Up
0 votes

· Last Updated

Comments

  • user14313
    user14313 ⚪️

    We get around this problem in a slightly long-winded way

     

    1. Create a new date calculated field called YoYDate with formula equal to:

    case when year(date) = year(curdate()) - 1 then date_add(date, interval 1 year) else date end

     

    Use this to power the x axis on the chart. It shifts all prior year data into this year.

     

    2. Create new metrics, e.g. if you had a metric called "Revenue" then create a calculated field called "This Year's Revenue" with the formula

    sum(case when year(date) = year(curdate())  then `revenue` else 0 end)

    And another called "Last Year's Revenue"

    sum(case when year(date) = year(curdate())-1  then `revenue` else 0 end)

     

    3. You can now plot these two metrics on the same chart, using only "The last year" as the date range. You will be able to see YoY metrics in this chart and YTD metrics in other charts.

  • frwern01
    frwern01 🟠

    This is pretty much how we do it now but I need the AMs to be able to use the page filter to select dates they want. Could be this year, last year or the year before. If I do not set dates on the cards this works fine for the "single year" cards. Any year we set in the page filter displays well. The issue comes up with the YOY cards that now only contain 1 year. If we set 2 years in the page filter (e.g. 2019 & 2018) the YOY cards display fine but the "single year" cards now have too much data. There is no formula to have the MAX Year display so a user could pick years 2017 and 2016 and the "single year" cards would display 2017 while the YOY cards would display both.

This discussion has been closed.

Categories