Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Group by + Date Based Table Calculations?

  • 18 October 2018
  • 2 replies
  • 2196 views
W

Here is the kind of data I have:


image



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!

2 replies

S
Rank

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

W

Thanks! This works

Reply


Terms of Service