I have a measure set up for calculating the average sales cycle of opportunities. It calculates the average off of a dimmension called Date Difference:
Here is the average measure:
When I use this measure in an explore, it is not calculating the total average correctly in some instances:
Has this happened to anyone else?
I think if you were to remove the date_difference and id dimensions from the query, your average would look like what you were expecting. In this case, you’re grouping by columns in the table that prohibit any meaningful aggregation to occur.
Hi Scott, when I remove those columns I get 180 as the response which is not the correct number. The average of those 3 numbers listed above should be 120. Any suggestions why it would calculate this number incorrectly?
Nothing obvious from the info you provide. Can you post the SQL that generates?
This may be a little messy, but here you go. Thanks for your help:
– use existing orders in looker_scratch.LR$7D1J9DNAH6GA9QIW458UE_orders
WITH sales_dashboard_opportunity AS (select o.*, (o.Amount / curr.Rate) as currentamount
from SalesDashboard_Opportunity o
left join SalesDashboard_CurrencyRates curr on cast(o.CreatedDate as date) = dateadd(d,1,curr.Date) and curr.Currency = o.CurrencyIsoCode
where type <> ‘Settled Ad Business’
)
, orders_bucketed AS (SELECT orders.orderid, o2.ContractId, o2.SfOpportunityId
–, o2.sowsigndate,
–o2.LastSOWCreateDate,
,o2.SowOrderLength, o2.BillingAddressCity, o2.isrenewal,
orders.BillingAddressCountryCode, orders.orderstate, orders.salesrep, orders.team, orders.revenueteam, orders.sfopptype,
orders.clientname, orders.clientid, orders.clientsfaccountid, orders.OrderDate, orders.OrderSource,
contracts.StartDate as sowstartdate,
–DATEADD(day,ROW_NUMBER() OVER (ORDER BY orders.orderid), ‘2016-09-05’) as sowdummydate,
IsNull(contracts.ActualTerminusDate, contracts.ScheduledTerminusDate) as sowenddate,
case when contracts.StateId in (2,3) then ‘SOW - Booked’ else ‘Non-SOW’ end as contractstate,
sum(orders.PriceUSD) as ordertotal,
--length here is saying, if order is NOT part of a contract then take max sku length of inventory item, if order IS part of contract take diff between Start* and end date.
--Start* = orderdate if the orderdate > contract start (if it's 2nd order on a contract)
(case when o2.ContractId is null
then max(orders.skulength)
else DateDiff(Day,
(case when orders.OrderDate > contracts.StartDate
then orders.OrderDate
else contracts.StartDate end)
, IsNull(contracts.ActualTerminusDate, contracts.ScheduledTerminusDate))
end) as max_sku_length,
count(distinct orders.AccountName) as num_products,
sum(case when orders.skuname like '%Company Page Ad%' or orders.skuname like '%Impressions%' then 1 else 0 end) as num_of_branding_products,
max(orders.currency) as currency,
sum(case when orders.skuname like '%top spot%' or orders.skuname like '%featured%' or orders.skuname like '%premium%' then 1 else 0 end) num_of_total_premium_products,
sum(case when orders.skuname like '%top spot%' or orders.skuname like '%featured%' or orders.skuname like '%premium%' then orders.priceusd else 0 end) sum_total_premium_products,
count(*) num_of_total_products,
sum(case when orders.SKUTypeId = 15 then 1 else 0 end) licensed,
sum(case when (orders.skuname NOT like '%Internship%' and (orders.skuname like '%Talent Starter%' or orders.skuname like '%Slot%')) then 1 else 0 end) has_listing,
sum(case when (orders.skuname like '%Talent Starter%' or orders.skuname like '%Candidate Search Access%') then 1 else 0 end) has_search,
sum(case when (orders.skuname like '%Company Page Ad%') then 1 else 0 end) has_pagead
FROM (
select
orders.orderid, orders.BillingAddressCountryCode, orders.orderstate, orders.salesrep, orders.team, orders.revenueteam, orders.sfopptype, orders.clientname, orders.clientid, orders.clientsfaccountid, orders.OrderDate,
orders.priceusd,
orders.skulength,
orders.AccountName,
orders.currency,
orders.OrderSource,
(case when upgradesku.name is null then orders.SKUName else upgradesku.name end) as skuname,
skus.SKUTypeId
from looker_scratch.LR$7D1J9DNAH6GA9QIW458UE_orders as orders
left join StackShop_InventoryItems ii on orders.inventoryid = ii.id
left join stackShop_skus upgradesku on ii.UpgradeToSKUId = upgradesku.id
left join stackShop_skus skus on ii.SKUId = skus.id
where orders.skuname not like '%enterprise%' and orders.skuname not like '%Services Fee%'
) orders
left join (
select id, ContractId, SfOpportunityId,
–sowsigndate,
–LastSOWCreateDate,
SowOrderLength, BillingAddressCity,
isrenewal
from StackShop_Orders
) o2 on o2.id = orders.orderid
left join StackShop_Contracts contracts on contracts.id = o2.contractid
where orders.orderstate <> ‘FullyRefunded’
group by orders.orderid, o2.ContractId, o2.SfOpportunityId,
–o2.sowsigndate,
–o2.LastSOWCreateDate,
o2.SowOrderLength, o2.BillingAddressCity, o2.isrenewal,
contracts.StartDate, contracts.ActualTerminusDate, contracts.ScheduledTerminusDate,contracts.StateId, contracts.id,
orders.BillingAddressCountryCode, orders.orderstate, orders.salesrep, orders.team, orders.revenueteam, orders.sfopptype, orders.clientname, orders.clientid, orders.clientsfaccountid, orders.OrderDate, orders.OrderSource
having sum(orders.PriceUSD) > 0
)
SELECT
TOP 500
AVG((DATEDIFF(day, (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CreatedDate ,120)), (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CloseDate ,120)))) ) AS “sales_dashboard_opportunity.average_sales_cycle”
FROM sales_dashboard_opportunity
LEFT JOIN orders_bucketed ON sales_dashboard_opportunity.Id = orders_bucketed.SfOpportunityId
WHERE (DATEDIFF(day, (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CreatedDate ,120)), (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CloseDate ,120))) > 0) AND (sales_dashboard_opportunity.StageName = ‘Closed Won’) AND ((((sales_dashboard_opportunity.CloseDate ) >= ((DATEADD(month,-6, CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102), 120),120)+’-01 00:00:00’, 120) ))) AND (sales_dashboard_opportunity.CloseDate ) < ((DATEADD(month,6, DATEADD(month,-6, CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102), 120),120)+’-01 00:00:00’, 120) ) )))))) AND ((((sales_dashboard_opportunity.CloseDate ) >= ((CONVERT(DATETIME2,‘2017-02-01’, 120))) AND (sales_dashboard_opportunity.CloseDate ) < ((DATEADD(month,1, CONVERT(DATETIME2,‘2017-02-01’, 120) )))))) AND ((((case
when orders_bucketed.BillingAddressCountryCode in (‘GB’, ‘IE’) then ‘UKI’
when orders_bucketed.BillingAddressCountryCode LIKE ‘FR’ then ‘FR’
when orders_bucketed.BillingAddressCountryCode in (‘AU’, ‘NZ’) then ‘AU/NZ’
when orders_bucketed.BillingAddressCountryCode in (‘DE’, ‘AT’, ‘CH’) then ‘DACH’
when orders_bucketed.BillingAddressCountryCode in (‘BE’, ‘LU’, ‘NL’) then ‘BLX’
when orders_bucketed.BillingAddressCountryCode in (‘US’, ‘CA’) then ‘US’
when orders_bucketed.BillingAddressCountryCode in (‘DK’, ‘FI’, ‘IS’, ‘NO’, ‘SE’) then ‘NORD’
else 'ROW' end)) = 'FR'))
– sql for creating the total
WITH sales_dashboard_opportunity AS (select o.*, (o.Amount / curr.Rate) as currentamount
from SalesDashboard_Opportunity o
left join SalesDashboard_CurrencyRates curr on cast(o.CreatedDate as date) = dateadd(d,1,curr.Date) and curr.Currency = o.CurrencyIsoCode
where type <> ‘Settled Ad Business’
)
, orders_bucketed AS (SELECT orders.orderid, o2.ContractId, o2.SfOpportunityId
–, o2.sowsigndate,
–o2.LastSOWCreateDate,
,o2.SowOrderLength, o2.BillingAddressCity, o2.isrenewal,
orders.BillingAddressCountryCode, orders.orderstate, orders.salesrep, orders.team, orders.revenueteam, orders.sfopptype,
orders.clientname, orders.clientid, orders.clientsfaccountid, orders.OrderDate, orders.OrderSource,
contracts.StartDate as sowstartdate,
–DATEADD(day,ROW_NUMBER() OVER (ORDER BY orders.orderid), ‘2016-09-05’) as sowdummydate,
IsNull(contracts.ActualTerminusDate, contracts.ScheduledTerminusDate) as sowenddate,
case when contracts.StateId in (2,3) then ‘SOW - Booked’ else ‘Non-SOW’ end as contractstate,
sum(orders.PriceUSD) as ordertotal,
--length here is saying, if order is NOT part of a contract then take max sku length of inventory item, if order IS part of contract take diff between Start* and end date.
--Start* = orderdate if the orderdate > contract start (if it's 2nd order on a contract)
(case when o2.ContractId is null
then max(orders.skulength)
else DateDiff(Day,
(case when orders.OrderDate > contracts.StartDate
then orders.OrderDate
else contracts.StartDate end)
, IsNull(contracts.ActualTerminusDate, contracts.ScheduledTerminusDate))
end) as max_sku_length,
count(distinct orders.AccountName) as num_products,
sum(case when orders.skuname like '%Company Page Ad%' or orders.skuname like '%Impressions%' then 1 else 0 end) as num_of_branding_products,
max(orders.currency) as currency,
sum(case when orders.skuname like '%top spot%' or orders.skuname like '%featured%' or orders.skuname like '%premium%' then 1 else 0 end) num_of_total_premium_products,
sum(case when orders.skuname like '%top spot%' or orders.skuname like '%featured%' or orders.skuname like '%premium%' then orders.priceusd else 0 end) sum_total_premium_products,
count(*) num_of_total_products,
sum(case when orders.SKUTypeId = 15 then 1 else 0 end) licensed,
sum(case when (orders.skuname NOT like '%Internship%' and (orders.skuname like '%Talent Starter%' or orders.skuname like '%Slot%')) then 1 else 0 end) has_listing,
sum(case when (orders.skuname like '%Talent Starter%' or orders.skuname like '%Candidate Search Access%') then 1 else 0 end) has_search,
sum(case when (orders.skuname like '%Company Page Ad%') then 1 else 0 end) has_pagead
FROM (
select
orders.orderid, orders.BillingAddressCountryCode, orders.orderstate, orders.salesrep, orders.team, orders.revenueteam, orders.sfopptype, orders.clientname, orders.clientid, orders.clientsfaccountid, orders.OrderDate,
orders.priceusd,
orders.skulength,
orders.AccountName,
orders.currency,
orders.OrderSource,
(case when upgradesku.name is null then orders.SKUName else upgradesku.name end) as skuname,
skus.SKUTypeId
from looker_scratch.LR$7D1J9DNAH6GA9QIW458UE_orders as orders
left join StackShop_InventoryItems ii on orders.inventoryid = ii.id
left join stackShop_skus upgradesku on ii.UpgradeToSKUId = upgradesku.id
left join stackShop_skus skus on ii.SKUId = skus.id
where orders.skuname not like '%enterprise%' and orders.skuname not like '%Services Fee%'
) orders
left join (
select id, ContractId, SfOpportunityId,
–sowsigndate,
–LastSOWCreateDate,
SowOrderLength, BillingAddressCity,
isrenewal
from StackShop_Orders
) o2 on o2.id = orders.orderid
left join StackShop_Contracts contracts on contracts.id = o2.contractid
where orders.orderstate <> ‘FullyRefunded’
group by orders.orderid, o2.ContractId, o2.SfOpportunityId,
–o2.sowsigndate,
–o2.LastSOWCreateDate,
o2.SowOrderLength, o2.BillingAddressCity, o2.isrenewal,
contracts.StartDate, contracts.ActualTerminusDate, contracts.ScheduledTerminusDate,contracts.StateId, contracts.id,
orders.BillingAddressCountryCode, orders.orderstate, orders.salesrep, orders.team, orders.revenueteam, orders.sfopptype, orders.clientname, orders.clientid, orders.clientsfaccountid, orders.OrderDate, orders.OrderSource
having sum(orders.PriceUSD) > 0
)
SELECT
TOP 1
AVG((DATEDIFF(day, (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CreatedDate ,120)), (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CloseDate ,120)))) ) AS “sales_dashboard_opportunity.average_sales_cycle”
FROM sales_dashboard_opportunity
LEFT JOIN orders_bucketed ON sales_dashboard_opportunity.Id = orders_bucketed.SfOpportunityId
WHERE (DATEDIFF(day, (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CreatedDate ,120)), (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CloseDate ,120))) > 0) AND (sales_dashboard_opportunity.StageName = ‘Closed Won’) AND ((((sales_dashboard_opportunity.CloseDate ) >= ((DATEADD(month,-6, CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102), 120),120)+’-01 00:00:00’, 120) ))) AND (sales_dashboard_opportunity.CloseDate ) < ((DATEADD(month,6, DATEADD(month,-6, CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102), 120),120)+’-01 00:00:00’, 120) ) )))))) AND ((((sales_dashboard_opportunity.CloseDate ) >= ((CONVERT(DATETIME2,‘2017-02-01’, 120))) AND (sales_dashboard_opportunity.CloseDate ) < ((DATEADD(month,1, CONVERT(DATETIME2,‘2017-02-01’, 120) )))))) AND ((((case
when orders_bucketed.BillingAddressCountryCode in (‘GB’, ‘IE’) then ‘UKI’
when orders_bucketed.BillingAddressCountryCode LIKE ‘FR’ then ‘FR’
when orders_bucketed.BillingAddressCountryCode in (‘AU’, ‘NZ’) then ‘AU/NZ’
when orders_bucketed.BillingAddressCountryCode in (‘DE’, ‘AT’, ‘CH’) then ‘DACH’
when orders_bucketed.BillingAddressCountryCode in (‘BE’, ‘LU’, ‘NL’) then ‘BLX’
when orders_bucketed.BillingAddressCountryCode in (‘US’, ‘CA’) then ‘US’
when orders_bucketed.BillingAddressCountryCode in (‘DK’, ‘FI’, ‘IS’, ‘NO’, ‘SE’) then ‘NORD’
else 'ROW' end)) = 'FR'))Yikes. There’s quite a bit going on. Unfortunately, I’m not in a position to pick apart this entire SQL statement; however, from the looks of it, the part that really matters is the last bit, which can be simplified to:
select avg(datediff(day, sales_dashboard_opportunity.CreatedDate, sales_dashboard_opportunity.CloseDate)) as average_sales_cycle
from sales_dashboard_opportunity
left join orders_bucketed
on sales_dashboard_opportunity.Id = orders_bucketed.SfOpportunityId
where (DATEDIFF(day, (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CreatedDate ,120)), (CONVERT(VARCHAR(10),sales_dashboard_opportunity.CloseDate ,120))) > 0)
and (sales_dashboard_opportunity.StageName = 'Closed Won')
and ((((sales_dashboard_opportunity.CloseDate ) >= ((DATEADD(month,-6, CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102), 120),120)+'-01 00:00:00', 120) ))) AND (sales_dashboard_opportunity.CloseDate ) < ((DATEADD(month,6, DATEADD(month,-6, CONVERT(datetime, CONVERT(VARCHAR(7),CONVERT(DATETIME, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102), 120),120)+'-01 00:00:00', 120) ) )))))) AND ((((sales_dashboard_opportunity.CloseDate ) >= ((CONVERT(DATETIME2,'2017-02-01', 120))) AND (sales_dashboard_opportunity.CloseDate ) < ((DATEADD(month,1, CONVERT(DATETIME2,'2017-02-01', 120) )))))) AND ((((case
when orders_bucketed.BillingAddressCountryCode in ('GB', 'IE') then 'UKI'
when orders_bucketed.BillingAddressCountryCode LIKE 'FR' then 'FR'
when orders_bucketed.BillingAddressCountryCode in ('AU', 'NZ') then 'AU/NZ'
when orders_bucketed.BillingAddressCountryCode in ('DE', 'AT', 'CH') then 'DACH'
when orders_bucketed.BillingAddressCountryCode in ('BE', 'LU', 'NL') then 'BLX'
when orders_bucketed.BillingAddressCountryCode in ('US', 'CA') then 'US'
when orders_bucketed.BillingAddressCountryCode in ('DK', 'FI', 'IS', 'NO', 'SE') then 'NORD' else 'ROW' end)) = 'FR'));
My recommendation to you would be to remove all filters and see if the average is what you’d expect. If so, then, one-by-one, add the filters back in and see which one throws a wrench in the works.
That join looks like it might be a one-to-many. If it is and it fans out the underlying table (sales_dashboard_opportunity), that may yield erroneous results. In which case, you’ll want to investigate symmetric aggregates.
Best of luck.
Hi Steven!
Looks like the awesome discourse community helped get you a little further along (thanks Scott!). If you need any additional help resolving your issue feel free to come on chat, we’re here to help!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
