How can I dynamically show YTD/QTD/MTD metrics in the KPI tiles on my dashboard?

Patterns
1

We can do this in Omni by leveraging the Parent Control functionality on the dashboard.

Adding a time frame control
To begin, we will create a control for our timeframe that we wish to swap between, such as a Created_At date. When we make this control, we will choose to have the month, quarter and year time parts available to us.

Next, we will make a second control to swap between a YTD, QTD and MTD count. These counts will each be a filtered measure that we can create in the workbook by duplicating a count measure, and adding a filter for YTD/QTD/MTD. Let’s first talk about making these counts.

Creating YTD/QTD/MTD filters:
These filters will be separate fields we can create by hidding + Add Field at the bottom of the field picker with the following logic in the case of YTD:

${date_field_name} <= OMNI_DAY_OF_YEAR(CURRENT_DATE - 1)

QTD and MTD could be created similarly with OMNI_DAY_OF_QUARTER and OMNI_DAY_OF_MONTH.

Creating filtered measures and constructing our query
Once we have these, we can apply them to our respective YTD/QTD/MTD measures via the UI.

Then, for a given tab we will add the default time part (in our case year), and our initial measure to the table, YTD Count. We will also limit the results to 2 rows in the top right using the limit drop down.

Then, we will select the KPI vis and go back to the dashboard.

Adding second and parent control
Now, we can go back to the dashboard and add a field selector control, selecting one of our new filtered measures such as YTD count. From that dialogue, we will also add the QTD Count and MTD count to our swapper.

Lastly, we will add a parent control. We will create three states for the parent control – (1) Year (2) Quarter and (3) Month. Then we will flip over to the Child menu to wire up the time frame selector to match year to year, quarter to quarter and month to month. After that, we’ll flip to the field selector to map YTD count to Year, QTD count to Quarter, and MTD count to Month. With that, we can hide the other two controls from the dashboard and we’ve got a functional Period to date swapper for our dashboard!

Longer form modeling example:

dimensions:
  now:
    sql: CURRENT_DATE()

  before_today_day_of_year:
    sql: ${users.created_at[day_of_year]} <= ${users.now[day_of_year]}

  before_today_day_of_quarter:
    sql: ${users.created_at[day_of_quarter]} <= ${users.now[day_of_quarter]}

  is_this_quarter_of_year:
    sql: ${users.created_at[quarter_of_year]} = ${users.now[quarter_of_year]}

  before_today_day_of_month:
    sql: ${users.created_at[day_of_month]} <= ${users.now[day_of_month]}

  is_this_month_number:
    sql: ${users.created_at[month_num]} = ${users.now[month_num]}

measures:
  users_count_created_at_this_year_to_date:
    aggregate_type: count
    filters:
      created_at:
        between_dates: [ this year, today ]

  users_count_created_at_this_quarter_to_date:
    aggregate_type: count
    filters:
      created_at:
        between_dates: [ this quarter, today ]

  users_count_created_at_this_month_to_date:
    aggregate_type: count
    filters:
      created_at:
        between_dates: [ this month, today ]

  users_count_created_at_last_year_to_date:
    aggregate_type: count
    filters:
      created_at:
        is: last complete year
      before_today_day_of_year:
        is: true

  users_count_created_at_last_year_quarter_to_date:
    aggregate_type: count
    filters:
      created_at:
        is: last complete year
      is_this_quarter_of_year:
        is: true
      before_today_day_of_quarter:
        is: true

  users_count_created_at_last_year_month_to_date:
    aggregate_type: count
    filters:
      created_at:
        is: last complete year
      is_this_month_number:
        is: true
      before_today_day_of_month:
        is: true
1 Like