Question

How to calculate consecutive count (based on various conditions)

  • 19 October 2021
  • 0 replies
  • 89 views

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!

 

TJ


This topic has been closed for comments