If you have a chart where you’d like to show the Top 3, 5, 15 or any number of values based on how they rank over a measure - this tutorial will show you how in a few short steps.
Turn this:
Into this:
Text based walkthrough with code
Step 1 ) Create a Query ranking your dimension over your measure
Step 2) Turn it into a Query View in omni
This will create a new view in your workbook model. You can look at it my going to Model > Model layers > Workbook and then you should find a file by the name you selected that looks something like this:
# Reference this view as example_rank_dimension
schema: PUBLIC
query:
fields:
account.billing_state: billing_state
calc_1: rank
opportunity.total_arr: total_arr
base_view: account
calculations:
calc_1:
sql: OMNI_FX_RANK(${opportunity.total_arr},
OMNI_OFFSET(${opportunity.total_arr}, -536870911, 0, 1073741823, 1))
sorts:
- field: opportunity.total_arr
desc: true
topic: account
# add this parameter to accept all topic filters into the inner CTE where the rank will be calculated
bind_all_filters: true
dimensions:
rank: {}
total_arr: {}
billing_state:
primary_key: true
measures:
count:
aggregate_type: count
Step 3) Join it back into the topic
base_view: account
joins:
sfdcuser: {}
opportunity: {}
example_rank_dimension: {} #our query view
relationships:
- join_from_view: account
join_to_view: example_rank_dimension
join_type: always_left
# join on the dimension you are ranking
on_sql: ${account.billing_state} = ${example_rank_dimension.billing_state}
relationship_type: many_to_one
Step 4) Add an ‘other’ bucket, and filter field to put the long tail into a single grouping
Back in the view file:
dimensions:
....
# prepend our rank to the dimension for better alphanumeric sorting
ranked_state:
sql: ${example_rank_dimension.rank} || ') ' || ${example_rank_dimension.billing_state}
hidden: true
# we apply our filter only field here, CASE/WHEN'ing the rank into the other bucket
# with Omni Templated filters
ranked_state_with_other_bucket:
sql: |
CASE
WHEN {{# example_rank_dimension.other_threshold.filter }}${example_rank_dimension.rank}{{/ example_rank_dimension.other_threshold.filter }}
THEN ${example_rank_dimension.ranked_state}
ELSE 'x) Other'
END
label: Billing State (Ranked by ARR)
......
# this 'filter only field' will allow us to place a condition on what ranks are individually shown
filters:
other_threshold:
type: number
label: Billing State Rank Threshold for 'Other Bucket'
description: Allows you to set criteria for when a rank will be shown, vs placed
Step 5) Optional – cleanup the topic to make more intuitive
We might want to make the ranked dimension appear like it’s all part of the same view. To do that we’ll hide some fields and apply view_labels and group_labels to our filter and add some special logic to control the number of padded zeros to ensure alphanumeric sorting is always correct.
Here’s the whole adjusted view file:
# Reference this view as example_rank_dimension
schema: PUBLIC
query:
fields:
account.billing_state: billing_state
opportunity.total_arr: total_arr
calc_1: rank
base_view: account
calculations:
calc_1:
sql: OMNI_FX_RANK(${opportunity.total_arr},
OMNI_OFFSET(${opportunity.total_arr}, -536870911, 0, 1073741823, 1))
sorts:
- field: opportunity.total_arr
desc: true
topic: account
bind_all_filters: true
dimensions:
total_arr:
hidden: true
rank:
description: Allows you to completely filter out states based on their ARR rank
group_label: Billing State
view_label: Account
pad:
sql: |
REPEAT('0', LENGTH(CAST({{ filters.example_rank_dimension.other_threshold.value }} AS VARCHAR))-1)
hidden: true
padded_rank:
sql: |
SUBSTRING(${example_rank_dimension.pad}, 1 * LENGTH(CAST(${example_rank_dimension.rank} AS VARCHAR))) || ${example_rank_dimension.rank}
hidden: true
ranked_state:
sql: ${example_rank_dimension.padded_rank} || ') ' || ${example_rank_dimension.billing_state}
hidden: true
billing_state:
hidden: true
primary_key: true
ranked_state_with_other_bucket:
sql: |
CASE
WHEN {{# example_rank_dimension.other_threshold.filter }}${example_rank_dimension.rank}{{/ example_rank_dimension.other_threshold.filter }}
THEN ${example_rank_dimension.ranked_state}
ELSE 'x) Other'
END
label: Billing State (Ranked by ARR)
group_label: Billing State
view_label: Account
filters:
other_threshold:
type: number
label: Billing State Rank Threshold for 'Other Bucket'
group_label: Billing State
description: Allows you to set criteria for when a rank will be shown, vs placed
into an Other Bucket
view_label: Account