Question

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: |
SUM(CASE
WHEN ${created_at} >= dateadd('month',-1,getdate()) and ${created_at} < getdate() THEN ${revenue}
END) /
SUM(CASE
WHEN ${created_at} >= dateadd('month',-2,getdate()) and ${created_at} < dateadd('month',-1,getdate()) THEN ${revenue}
END)

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
filters:
created_at: this month

- measure: revenue_last_month
...
hidden: true
filters:
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!

Reply