Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Drill in Measures with CASE statements

  • 27 June 2019
  • 4 replies
  • 2415 views
kehv1n

Hello! I am currently having an issue fixing a little drill down bug at work.


I currently have a measure in my view that calculates whether two dimensions are greater than the other and in another clause, whether that dimension is greater than 0. It currently works and calculates correctly however the drill downs are not bringing up the how that number was calculated. Is there any way to implement that functionality in the dimension below?


I understand that for “filters” to carry over to drill downs they must be in LookML filters however I have a CASE statement that needs to carry over.


measure: count_pga_met {

type: sum

sql: CASE WHEN (${TABLE}.pga_earned >= ${TABLE}.pga_req) AND (${TABLE}.pga_req > 0) THEN 1 ELSE 0 END ;;

drill_fields: [detail*]

}

Note: pga_earned and pga_req were measures but are now dimensions of the table.


 measure: pga_new {

type: sum

sql: ${TABLE}.pga_new ;;

label: "PGA New"

drill_fields: [detail*]

}



  measure: pga_recovery {

    type: sum

    sql: ${TABLE}.pga_recovery ;;

    label: "PGA Recovery"

    drill_fields: [detail*]

  }



  dimension: pga_req {

    type: number

    sql: ${TABLE}.pga_req ;;

    label: "PGA Required"

    drill_fields: [detail*]

  }

Thank you so much for the time + hasty replies. Thankful for this community.


Cheers


Kevin

4 replies

izzy
Userlevel 7
Badge +1

@paola wrote up a wee explanation of this over here:



In a nutshell: Case statements don’t pass along to drills, but filters: parameters defined in the LookML do, so using filters will make your drills match up with your expected results.

kehv1n

Izzy,


Thanks so much for getting back to me. Yes, I have seen (and been studying her post) for some time now.


I have currently done this for some of the CASE statements we have because they’re relatively simple and don’t involve multiple dimensions:


  measure: count_total_req {

    type: count

    # sql: CASE WHEN (${TABLE}.total_msr_req > 0) THEN 1 ELSE 0 END ;;

    filters: {

      field: total_msr_req

      value: ">0"

    }

    drill_fields: [detail*]

  }

As you can see the filter key takes place of the previous sql key. But what do I do when the CASE statement in the SQL isn’t as a simple and involves another dimension? :


 measure: count_total_met {

    type: sum

    sql: CASE WHEN (${TABLE}.total_msr_earned >= ${TABLE}.total_msr_req) AND (${TABLE}.total_msr_req > 0) THEN 1 ELSE 0 END ;;

    drill_fields: [detail*]

  }

Thank you!

kehv1n

Team,


I have figured out how to implement the functionality needed.


In order to maintain the CASE statement in your drill down, you must create a dimension that calculates the CASE and filter on that dimension. As below:


     dimension: greater_than_msr_req {

    type: yesno

    case: {

      when: {

        sql: ${total_msr_earned} >= ${total_msr_req} ;;

        label: "yes" # returns the label if true (type string)

      }

    }

    hidden: yes

  }



  measure: count_total_met {

    type: count

    # The filters below are implementing the functionality of the SQL below

    # CASE WHEN (${TABLE}.total_msr_earned >= ${TABLE}.total_msr_req) AND (${TABLE}.total_msr_req > 0) THEN 1 ELSE 0 END 

    filters: {

      field: total_msr_req

      value: ">0"

    }

    filters: {

      field: greater_than_msr_req

      value: "'yes'" # filter the drill to only the values with a 'yes' string

    }

    drill_fields: [detail*]

  }

Thank you again @izzy for making me think a little harder


Cheers,


Kevin D.

izzy
Userlevel 7
Badge +1

Ah, thanks for sharing the final solution Kevin— Looks perfect.

Reply


Terms of Service