How to calculate consecutive count (based on various conditions)

  • 19 October 2021
  • 0 replies

Hi Guys,


Looker rookie here. I’m trying to determine a way to calculate a consecutive count and am not sure how to go about it. My data is structured as follows:


Month | Person | Award

2019-01, Yoda, Master

2019-05, Skywalker L, Master

2021-06, Han Solo, Rebel

2020-04, Darth Vader, Sith


I can currently calculate the total count of an award based on a particular month using the following:


  dimension: monthfilter {
    label: "Yes/No"
    type: yesno
    sql: extract (month from ${TABLE}.month) = 6 ;;
    hidden: yes

  dimension: uptonow {
    type: yesno
    sql:   ${TABLE}.month < current_date() ;;
    hidden: yes


  measure: mastercount{
    label: "Master Count"
    type: count_distinct
    sql: ${month_month} ;;
    filters: [monthfilter: "Yes",Award: "Master",uptonow: "Yes"] 


Would someone be able to point me in the right direction in order to answer the following:

  • Number of consecutive times a person has achieved X award status at the end of the FY
  • What start and end year the ‘streak’ occurred


Any help would be much appreciated!



This topic has been closed for comments