Single Value Visualization with percent_of_previous Measure

Userlevel 2

I’m trying to create a Single Value visualization displaying the ratio of the current month’s revenue to the prior month’s. Is there a trick to making this work? I need multiple months to generate the percent_of_previous measure, but including multiple months makes it impossible to use the Single Value visualization.

3 replies

I think there are two ways to do this, but this is the one I would favor:

 - measure: compare_last_months_revenue_to_this_month
label: 'III Revenue Comparison (monthly)'
sql: |
WHEN ${created_at} >= dateadd('month',-1,getdate()) and ${created_at} < getdate() THEN ${revenue}
END) /
WHEN ${created_at} >= dateadd('month',-2,getdate()) and ${created_at} < dateadd('month',-1,getdate()) THEN ${revenue}

This example will include a complete previous month and only part of this month depending on what day you run it, so you may want to consider adding some additional logic so you’d comparing apples to apples. We generally do last 30 days, then 30 to 60 days, and 60 to 90 day chunks instead of calendar months.

we’ve accomplished this using three measures: a hidden measure calculating the revenue with a filter on the current month, another hidden measure calculating the revenue with a filter on the previous month, and the last measure referencing both of the hidden measures.

  - measure: revenue_this_month
hidden: true
created_at: this month

- measure: revenue_last_month
hidden: true
created_at: last month

- measure: revenue_comparison_this_month_last_month
type: number
sql: 100.0 * ${revenue_this_month} / ${revenue_last_month}
value_format: '#.0\%'
Userlevel 2

Great tips, thanks!