Group by + Date Based Table Calculations?


(WThomas) #1

Here is the kind of data I have:

Days to deliver is a table calculation.

Here is the visualization I want:
image

However, because the ‘days to deliver’ calculation needs each Order Date and Deliver Date combo included in the data table to process, this is the closest I can get to what I want:

image

Looker seems to require displaying one row for every Order - Deliver Date / Store combination, so if we have 100s of orders this will have hundreds of rows of repeating information. Is there a way to group by store number in this example without displaying superfluous multiple rows? Or is there a work-around?

Thanks for any help!


(Spencer White) #2

Hey WThomas, thanks for reaching out.

So for this I would recommend doing the datediff in LookML and creating a measure of type: average for Average Days to Deliver.

The datediff can look something like this(depending on dialect):

dimension: days_to_deliver {
type: number
sql: datediff(days,${order_date},${deliver_date}) ;;
}

Then the average would look like this:

measure: average_days_to_deliver {
type: average
sql: ${days_to_deliver} ;;
}

Finally, you’ll want to only include the Store Number dimension and the Average Days to Deliver measure in the Explore. Other dimensions included in the query will be added to the group by and may ‘fan out’ the data, potentially throwing off the average.

Hope this helps,

Spencer


(WThomas) #3

Thanks! This works