I have daily sales data (from Salesforce, if that’s helpful) that I’d like to summarize on quarterly, regional dimensions.
Consider the following sales table -- we’ll call this explore “Accounts and Opportunities”
I also have the following quarterly, regional sales targets table - we’ll call this explore “Sales Targets”
I’d like to join these data sets so that I can create the following tables as looks for my end users:
When I join these two explores (on region & year-quarter), I get the following when I try to summarize:
We can see the target amount is way off from above -- in fact it’s a multiple of the number of sales records we have because the join did the following:
I’m now incorrectly summing the quarterly target as if it’s a daily amount like the sales data.
What’s the best way to combine these two data sets so that I can get to my ideal summary results as shown above?
Keep in mind that there are a number of other dimensions from the “Accounts and Opportunities” table that my users may want to filter on. There are also many more regions. I want to avoid doing this all through table calculations as it’s incredibly manual and inflexible. I want to be able to maintain my “Sales Targets” table outside of Looker.
Thanks!
The answer is changing the measure in my view:
Previous:
measure: target_amount {
type: sum
sql: ${TABLE}.target_amount ;;
}
Updated: changed sum to sum_distinct
measure: target_amount {
type: sum_distinct
sql: ${TABLE}.target_amount ;;
}