Other buckets using Table Calculations

other
vis
bucket
pie

(Zev Lebowitz) #1

If you’re building a chart or table off of a high-cardinality dimension, you might want to lump all low-frequency dimensions into an “Other” bucket, to avoid cluttering your output. We’ve posted [solutions for this using derived tables] (Create an “Other” bucket in a chart), but the ::total table calculation makes other bucketing possible without any LookML.

Starting off with traffic to each landing page on our website:

The top 3 pages generate the lions share of traffic, but several hundred others make up the remainder. A pie chart with this table would be very messy, so we can add a row limit to display a more manageable data set:

Any visualization we build off of this table though will be misleading because we’ve excluded traffic to pages outside of the top 10.

Using the Table Calculations, we can replace the last row in this table with the sum of traffic from all rows excluded from the table:

  1. First make sure to turn on column totals (the check box next to the Row Limit), so we can access total visits across all landing pages in Table Calculations

  2. Add a table calculation to display Event Count for all non-displayed landing pages in the last row of the table:

if(
# For rows other than the last row
   row() != max(row())  
# Display traffic to that page
   ,${events.count}
# For the last row, display total traffic minus traffic from all rows above this one
   , ${events.count:total} - sum( if(row()= max(row()) , 0 , ${events.count}) )
   )
  1. Add another table calculation to display "Other"as the Landing Page in the last row
if(
# For rows other than the last row
   row() != max(row())  
# Display landing page URL
   ,${session_landing_page.full_page_url}
# For the last row, display "Other"
   , "Other"
   )
  1. Hide the original dimension and measure, and choose the visualization option of your choice:

Since the logic to determine the last row is based off of the current maximum row number, any changes to the row limit will flow through to the other bucketing logic automatically.


2016 April Discourse Digest
Adjustable Pie chart visualizations
(Willemijn) #2

Thanks Zev, works like a charm.

One question though: is it possible to make the “Traffic (with other bucket)” measure clickable? I.e.: can I somehow define that the “Traffic (with other bucket)” measure should have the same drilldown fields as the original Events Count?

Willemijn.


(Zev Lebowitz) #3

Hi Willemijn,

That’s a really great question! There is not currently a way to add drilling behavior to a table calculation, but I know it’s something we’ve thought about before. I’ll make sure our product team is aware of the suggestion!

Thanks,
Zev


#4

+1 to adding drill fields to table calculations

Alternatively, being able to add groupings - at a visualization level - to the long tails of histograms or dimensions which fall outside of a particular density would also address this need.


(Adam Beckerman) #5

+1 for adding drill fields to table calculations


(Paola) #6

I’ll let our product team know you’d like to have this @adamb_spring!


(Michael Liang) #7

+1 on adding drill fields to table calculations


(Richard Knobloch) #8

+1 on adding drill fields to table calculations


(Jordan K) #9

+1 on adding drill fields to table calculations


(Magda Paszko) #10

+1 on adding drill fields to table calculations


(Robin) #11

+1 on adding drill fields to table calculations