Controlling Cohort Grid Ordering Via Cross Joins

When determining the ordering of rows and columns in cohort grids, Periscope examines the order of your underlying data. In most cases, this produces a result in the desired order. Sometimes however, there's that one pesky column that looks like it's in the wrong spot. What's going on here?

Turns out, the underlying data doesn't have a value associated with that pivot for the first row. In other words, if I have data for January, February, April, etc but not March for the first year I am showing data, then March will appear at the end of the list of months.

How do we work around this? By creating a list of combinations via a cross join and left joining our data set onto this list of combinations! The below snippet creates a cross join to generate a comprehensive list of days and weeks from our demo database (a hypothetical gaming company)

First, we need to generate a list of weeks:

with
  t1 as (
    select distinct
      [created_at:week] as week_of
    from
      purchases
  )

Then, we need to generate a list of days:

  , t2 as (
    select distinct
      extract(dow from created_at) as ordering
      , case
        when extract(dow from created_at) = 0
          then 'Sunday'
        when extract(dow from created_at) = 1
          then 'Monday'
        when extract(dow from created_at) = 2
          then 'Tuesday'
        when extract(dow from created_at) = 3
          then 'Wednesday'
        when extract(dow from created_at) = 4
          then 'Thursday'
        when extract(dow from created_at) = 5
          then 'Friday'
        else 'Saturday'
      end as day_of_week
    from
      purchases
  )

Putting them together in the cross join:

, combinations as (
  select
    t1.week_of
    , t2.day_of_week
  from
    t1
    , t2
  order by
    1
    , t2.ordering
)

After this, we just need to make sure to "Preserve Sort Order" in the "Format Chart" tab on the right hand side of our editor. Once we have this list, we just need to left join our data set and voila! Perfectly ordered cohort grids!

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • It would be also nice if cohort grid detects and  then hide when both row and column is empty. 

    Reply Like
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 1 yr ago
      • Reported - view

      Himmet Kaplan To ensure I'm understanding correctly, are you describing a case where empty rows/columns collapse in Cohort Grids? This is definitely a great suggestion!

      Reply Like
    • Neha Kumar especially If cohort depends on filters like country there might be empty rows and columns in beginning for some countries and its hard to write CASE for each country. If cohort grid detects and hides when both rows and columns empty, that would be great.

      Reply Like 1
Like1 Follow
  • 1 Likes
  • 1 yr agoLast active
  • 3Replies
  • 1134Views
  • 2 Following