Chart Type - Bullet Charts

I love a great benchmark or comparison to goals. One solution that Kyle on my team came up with was a bullet chart. I standardized this with a Parameterized SQL Snippet to do all the transformation heavy lifting for you 🙂

Name: bullet_chart(table,value_field,benchmark_field,dimension_field)

select
  case
    when [value_field] < [benchmark_field]
      then [benchmark_field] - [value_field]
    else null
  end as value
  , 'to benchmark' as label
  , [dimension_field]
  , rank() over(order by [value_field]) as ord
from
  [table]
union all
select
  case
    when [value_field] > [benchmark_field]
      then [benchmark_field]
    else [value_field]
  end as value
  , '[value_field]' as label
  , [dimension_field]
  , rank() over(order by [value_field]) as ord
from
  [table]
union all
select
  case
    when [value_field] >= [benchmark_field]
      then [value_field] - [benchmark_field]
    else null
  end as value
  , 'passed benchmark' as label
  , [dimension_field]
  , rank() over(order by [value_field]) as ord
from
  [table]
order by 2 desc, 4 desc
3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Loving the comparison to goals  Wai Pui but the issue I encounter is what do you do if they pass the benchmark. One solution that Kyle on my team came up with was a bullet chart. I standardized this with a Parameterized SQL Snippet to do all the transformation heavy lifting for you 🙂

    Name: bullet_chart(table,value_field,benchmark_field,dimension_field)

    select
      case
        when [value_field] < [benchmark_field]
          then [benchmark_field] - [value_field]
        else null
      end as value
      , 'to benchmark' as label
      , [dimension_field]
      , rank() over(order by [value_field]) as ord
    from
      [table]
    union all
    select
      case
        when [value_field] > [benchmark_field]
          then [benchmark_field]
        else [value_field]
      end as value
      , '[value_field]' as label
      , [dimension_field]
      , rank() over(order by [value_field]) as ord
    from
      [table]
    union all
    select
      case
        when [value_field] >= [benchmark_field]
          then [value_field] - [benchmark_field]
        else null
      end as value
      , 'passed benchmark' as label
      , [dimension_field]
      , rank() over(order by [value_field]) as ord
    from
      [table]
    order by 2 desc, 4 desc
    Reply Like 2
  • Too bad Periscope does not have the Stephen Few bullet chart out of the box. 

    Reply Like
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 5 mths ago
      • Reported - view

      Ricky Ng There's something similar to the Stephen Few bullet chart in the community post here, check it out!

      Reply Like
Like2 Follow
  • 2 Likes
  • 5 mths agoLast active
  • 3Replies
  • 795Views
  • 3 Following