Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Table Calc Conversion Rate for Opps - OppsWon

  • 23 August 2017
  • 6 replies
  • 516 views
W

Hi all - I’m new the community. I’m sure this is something simple, but I’m getting stuck.


I have a table calculation I’m trying to run to calculate the conversion rate of Opportunities to Closed/Won opportunities grouped by Created Week.


In SQL this would be done like so:



SELECT

COUNT(DISTINCT CASE WHEN StageName = ‘Closed/Won’ Then Opp.ID ELSE NULL) / COUNT(DISTINCT Opp.ID) AS ‘Opp to Won %’

FROM

Salesforce_Opportunity AS OPP

GROUP BY

Opp.CreatedWeek



But in trying to figure out how to do this in table calculations, I’m getting stuck with figuring out how to do the “CASE WHEN” in a way that doesn’t require me to add the StageName field to the layout (which would be ok, but it throws off the calculation for the CreatedWeek).


Can anyone help, please?


Thanks,

Wyatt

6 replies

rachel.johnson
Rank
Userlevel 2

Hey @wyatt, welcome to the community!


For table calculations all components of the calculation must be part of the table since the calculation is done after the results from the query are generated.


In this situation your best bet would be to create a measure that calculates the conversion rate. When Looker generates the SQL it will take the sql: parameter from the measure and insert it into the SELECT statement, just as you did above.

W

Thanks, Rachel - do you have an example (in Psuedo Code) of what that might look like?

W

I have the below in my lookML model, but all I get is zeros when I run the look. I added in the calculation “${opportunity.wonPct}” to the look, and it has a number, but it doesn’t properly calculate it on the dimension I’ve added to the look.


Any suggestions?



measure: wonPct {

type: number

sql: (

SELECT COUNT(DISTINCT CASE WHEN StageName = ‘Closed/Won’ Then Opp.ID ELSE NULL END) / COUNT(DISTINCT Opp.ID)

FROM global_temp.salesforce_opportunity_latest_s AS Opp

) ;;

drill_fields: [detail*]

}


rachel.johnson
Rank
Userlevel 2

@wyatt Definitely.


Basically, you want to create three measures: the numerator, the denominator, and the rate. For the numerator measure you would use filters: to count only the specific ids you want. The filters: parameter cannot be applied to type: number so we want to calculate the numerator first then use it for the rate calculation.


Here’s a generalized example of how to approach this:


measure: rate {

type: number

sql: ${numerator} / ${denominator} ;; }



measure: denominator {

type: count_distinct

sql: ${id} ;; }



measure: numerator {

type: count_distinct

sql: ${id} ;;

filters: {

field: stage

value: "won" } }
W

Perfect! I hadn’t used the “filters:” syntax before, but the design pattern you present here makes complete logical sense in retrospect.


Thank you so much for your help, Rachel!

rachel.johnson
Rank
Userlevel 2

@wyatt Happy to help!

Reply


Terms of Service